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/
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:
Post a Comment