Hang Time – Disconnect a session

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
from v$session
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;

or

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.

Please follow and like: