Removing Oracle Sessions

There are times when you need to drop the connection for a user or process in the database, we will review the basic forms to perform these tasks;

 

To drop a session in Oracle use the traditional way

— Sintax —

alter system kill session (sid, serial #) [immediate]

— Example —

ALTER SYSTEM KILL SESSION ‘5340, 43713 ‘;

 

A special case is KILL command for RAC enviroments, where you add a third number with @ prefix to specific the instance number inside the cluster, you can see this sid in the inst_id from gv$session view

— Syntax —

alter system kill session ‘sid , serial# ,@instance_id’ [immediate]

— Kill session 5427 in node 1 —

alter system kill session ‘5427,14599,@1’

— Kill session 3588 in node 2 —

alter system kill session ‘3588,14088@2’

 

Another way is to use the kill connections disconnect session, this system is primarily designed for MTS connections, because if  the session we drop to kill dispatcher, eliminating all connections through this dispatcher, which can be a high number of the order of tens or hundreds

— Syntax —
alter system disconnect session ‘sid , serial#’ [POST_TRANSACTION] [IMMEDIATE]

— Example —
ALTER SYSTEM DISCONNECT SESSION ‘5340, 43713 ‘ ;

 

  • The POST_TRANSACTION setting (only for DISCONNECT SESSION) allows ongoing transactions to complete before the session is disconnected. If the session has no ongoing transactions,
  • The IMMEDIATE setting (for both, DISCONNECT SESSION and KILL SESSION) disconnects the session and recovers the entire session state immediately, without waiting for ongoing transactions to complete

if you also specify POST_TRANSACTION and the session has ongoing transactions, then the IMMEDIATE keyword is ignored.

 

Of course, kill sessions from within the database many times, sessions want to live forever and not want to die, others die but starting doing rollback for a while… Another way is to remove connections from the OS. usually in Unix with KILL command, or OpenVMS with the STOP command. Ok, this is a much more radical way but in medium or large databases is the most efficiently.

# Example

kill -9 7954

 

 
Microsoft Windows is a special case where the oracle has ORAKILL (execute from Windows MsDos) command

REM Syntax —

orakill sid thread

REM Example —

orakill ORCL 1234

 

HTH – Antonio NAVARRO

 

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