Sunday, 15 March 2015

Clearing Dead-marked for killing session in Oracle

Issue: 

We kill the blocking session from Oracle but even after killing the session was not cleared by PMON.
So the blocking session still host the lock.

SQL> alter system kill session '250,87658'
*
ERROR at line 1:
ORA-00031: session marked for kill

Solution:

Steps to clear

++ Find the SPID of the session
SQL> SELECT P.SPID, S.SID, S.SERIAL# FROM V$PROCESS P, V$SESSION S WHERE P.ADDR = S.PADDR AND S.SID = '&sid';

++ Set oradebug for the SPID
SQL> oradebug setospid <SPID>;

++ Check fro short stack of the session.
SQL> oradebug short_stack;

++ Crash the SPID,Now the session will be cleaned by PMON.
SQL> oradebug event immediate crash;

++ Try to check the session and by now lock will be released.

Ref: http://blog.tanelpoder.com/2008/06/19/killing-an-oracle-process-from-inside-oracle/


No comments: