ALTER SYSTEM KILL SESSION

Note : Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed.

 

There could be a number of reasons to kill non-essential Oracle user processes. In Oracle the alter system kill session command allows us to kill these Oracle sessions. 

The alter system kill session command requires two unique arguments that uniquely identify the Oracle session, the session identifier and serial number.

First you have to identify the session to be killed with alter system kill session.

select SID,SERIAL#,STATUS,SCHEMANAME,PROGRAM from v$session;


The SID and SERIAL# values of the Oracle session to be killed can then be substituted and the alter system kill session command issued.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';


Sometimes Oracle is not able to kill the session immediately with the alter system kill session  command alone. Upon issuing the alter system kill session  command, the session will be 'marked for kill'. It will then be killed as soon as possible.


In the case of a session being 'marked for kill' after issuing the alter system kill session  command and not killed immediately, the alter system kill session  command can be forced by adding the immediate keyword:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; 

In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

References :

• Automate kill SNIPED sessions
•  maximum number of processes exceeded