There are many reasons why a dba has to kill a session. That is pretty ease but not always successful.
But why it is not always successful?
An alter system kill session ‘<SID>,<SERIAL#>’; may not kill the session because that’s not what the commands does. The command sets a flag in the session which will make the session “commit suicide”.
There are two reasons why a session would “ignore” the flag:
- The session realizes the flag and starts undoing the changes (not really ignoring the flag).
- The session never checks for the flag which is an abnormal behavior (ignoring the flag).
Let’s look a bit deeper into option 1:
When the session realizes the kill flag, it starts rolling back the open transaction. Normally transactions should be kept small but this is not the standard in production environments. If the transaction is huge this can even take hours.
SQL> @sw 263 SID STATE EVENT SEQ# SEC_IN_WAIT P1 ------- --------------------------- ------------- ----------- ------ 263 WAITING inactive session 140 1584 152
If the session is busy rolling back, you should wait until it is done.
Now we come to option 2:
In the above example the session is idle and not rolling back. This means we safely kill the session’s OS process.
First we have to figure out which process is the correct OS process of this session:
SELECT s.sid, s.serial#, s.username, s.osuser, p.spid, s.machine ,p.terminal, s.program FROM v$session s, v$process p WHERE s.paddr = p.addr AND s.sid=:sid ;
Column spid of this query shows the id of the dedicated server. We can now go ahead and kill the process with “kill -9 <spid>”. On Windows systems use orakill %ORACLE_SID% <spid> command (run cmd.exe with administrative privileges).
If the session is still seen in v$sessions you can wake up pmon to clean up the session with the following command from SQL*Plus:
$ sqlplus / as sysdba SQL> oradebug wakeup <process# of pmon>;
To get the process# of pmon execute the following sql:
SELECT pid FROM v$process p, v$bgprocess b WHERE b.paddr = p.addr AND name = 'PMON' ;
In most cases the process# is 2.