The other day I was installing Oracle Enterprise Manager 12c Cloud Control and ran into a few problems. I had to scrub the install and start from scratch due to a naming issue in the /etc/hosts file (story for another time). In order to start over, I had to remove the SYSMAN and associate schemas from the repository database. When I went to drop the schema, the database said the schema was still connected and active. Besides being frustrated at this point, I had to identify and kill the sessions that were still active for the SYSMAN schema.
Any time when needing to find active sessions within the database, we can use the V$SESSION view. The only thing we need to grab from this view is the SID and SERIAL#. The SQL I used to get this is below:
select sid, serial#, username
where username like ‘SYSMAN%’
and status = ‘ACTIVE’;
This will pull all the sessions that are tied SYSMAN. Being that this was an Oracle Enterprise Manager 12c repository, there are a few different SYSMAN schemas we need to account for.
Once we have all the SID and SERIAL#; we need to disconnect the session from the database. Yes, we can do an ‘alter system kill session‘, but this would abandon the transaction for the session, if any. To cleanly disconnect the session, we can use the ‘alter system disconnect session‘ command. This command has been around since 8i, and does a good job of disconnecting a session cleanly. The syntax for the command is listed below:
alter system disconnect session ‘sid,serial#’ immediate;
alter system disconnect session ‘sid,serial#’ post_transaction;
Now that the sessions that were hanging me up have been disconnected, I can clean up the OEM repository and prepare to restart the installer.
I’m Bobby Curtis and I’m just your normal average guy who has been working in the technology field for awhile (started when I was 18 with the US Army). The goal of this blog has changed a bit over the years. Initially, it was a general blog where I wrote thoughts down. Then it changed to focus on the Oracle Database, Oracle Enterprise Manager, and eventually Oracle GoldenGate.
If you want to follow me on a more timely manner, I can be followed on twitter at @dbasolved or on LinkedIn under “Bobby Curtis MBA”.