ORA-00031: session marked for kill

SQL> ALTER SYSTEM KILL SESSION '222,18784' immediate;

ALTER SYSTEM KILL SESSION '222,18784' immediate
*
ERROR at line 1:

ORA-00031: session marked for kill

* Here the issue is in what this alter system kill command is doing. 

* Alter System Kill command > It’s not actually killing the target session (like kill -9 would do for OS processes). 

* It just sets a bit in the target sessions state object, which marks that the target session should end. 

* But its entirely with the target session to check this bit and act on it.

* All the kill session command is doing is ASK the target session to clean up and exit – via setting that bit.

* The session trying to issue the kill will hang for 60 seconds and then return this “session marked for kill” message. And the target session does not get killed at all.

* The “ORA-00031: session marked for kill” message you see after 60 seconds just means that:

* Session marked for  “kill” bit in target sessions state object. Session waits the target session to die for 60 seconds and times out after it doesn't happen It returns “session marked for kill” error.

* In this situation we can find out the PID kill and kill it like below

SQL> show user;

USER is "SYS"

SQL> select name from v$database;

NAME

---------------------------

VKRK11

SQL> SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S WHERE P.ADDR = S.PADDR AND S.SID = 222;

SPID                                        SID    SERIAL#

------------------------------------ ---------- ----------

14235                                       222      18784

SQL> ! kill -9 14435

SQL> SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S WHERE P.ADDR = S.PADDR AND S.SID = 222;

no rows selected

Its killed now .. 

Thank You!