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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Pardy DBA

ORA-00001: unique constraint (ORA.BLOG_TAGLINE_PK) violated

Martin Widlake's Yet Another Oracle Blog

Oracle performance, Oracle statistics and VLDBs

HeliFromFinland

Heli's thoughts on Database Designing, Oracle SQL Developer Data Modeler, User Groups etc.

Julian Dontcheff's Database Blog

The good DBA is one who learns from his mistakes, the best DBA is one who learns from other DBA's mistakes

Martins Blog

Trying to explain complex things in simple terms

The Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

PeteWhoDidNotTweet.com

Stuff that interests me, if not you!

Uwe Hesse

about Database Technology

%d bloggers like this: