ORA-00031:session marked for kill（標記要終止的會話）解決方法

xingfei80發表於2014-07-24
Session會話
今天碰到一個問題，有一張表不能操作，很可能是被鎖了，首先想到的是kill session，於是執行了下列的指令碼找到是哪個session有問題：
檢視錶是否被鎖
SELECT /*+ rule*/
a.sid, b.owner, object_name, object_type
FROM v$lock a, all_objects b
WHERE TYPE = 'TM'
and a.id1 = b.object_id;

根據上面查詢出的sid，找出對應的serial#:
SELECT sid,serial# FROM v$session WHERE sid = &sid;

發現有一個會話有鎖sid 197,serial# 17，於是執行alter system kill session ‘197,17’;大概等了30s中，pl/sql developer報出一個錯誤:ora-00031:標記要終止的會話。

解決方法：查出session的spid
select spid, osuser, s.program from v$session s, v$process p where s.paddr = p.addr and s.sid =197;
1. 在linux上，  kill -9 12345
 
2. 在上，C:\Documents and Settings\gg>orakill orcl 12345
 
orcl：表示要殺死的程式屬於的例項名
 
12345：是要殺掉的執行緒號
 
ORA-00031: session marked for kill
 
Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot bekilled immediately (because it is rolling back or blocked on a networkoperation), but it has been marked for kill. This means it will be killed as soonas possible after its current uninterruptible operation is done.
 
Action: No action is required for the session to be killed, but further executionsof the ALTER SYSTEM KILL SESSION command on this session may cause the sessionto be killed sooner.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14663377/viewspace-1226186/，如需轉載，請註明出處，否則將追究法律責任。

相關文章