ORA-00031: session marked for kill

We faced a issue on our test database that one of user requested to kill his sesssion when try “alter system kill session”

ERROR at line 1:
ORA-00031: session marked for kill

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.

We can check if the session is doing any rollback at the time using the query below:
SQL> SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
 FROM v$session a, v$transaction b
 WHERE a.saddr = b.ses_addr and a.sid=&sid;             –>>> Enter the blocking session SID here (&sid:).
In that situation we need to find the process and kill OS process.
select spid
from v$process a , v$session b
where a.addr=b.paddr and b.sid=&sid;

> Enter the  SID here (&sid:).
OS Kill Command:
on windows

D:> orakill ORACLE_SID spid

On Unix
$ kill -9 spid

For more information refer MOS DOC Id: Kill A Process – Not able to clear session (Doc ID 1493261.1)

Thanks for reading ..

  • July 14, 2017 | 18 views
  • Comments