ora-00031:session marked for kill处理oracle中杀不掉的锁

一些ORACLE中的进程被杀掉后，状态被置为"killed"，但是锁定的资源很长时间不释放，有时实在没办法，只好重启数据库。现在提供一种方法解决这种问题，那就是在ORACLE中杀不掉的，在OS一级再杀。

1.下面的语句用来查询哪些对象被锁：

select object_name,machine,s.sid,s.serial# 
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;

2.下面的语句用来杀死一个进程：
alter system kill session '24,111'; (其中24,111分别是上面查询出的sid,serial#)

【注】以上两步，可以通过Oracle的管理控制台来执行。

3.如果利用上面的命令杀死一个进程后，进程状态被置为"killed"，但是锁定的资源很长时间没有被释放，那么可以在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号：
select spid, osuser, s.program 
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24 （24是上面的sid）

4.在OS上杀死这个进程（线程）：
1)在unix上，用root身份执行命令: 
#kill -9 12345（即第3步查询出的spid）
2)在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：
orakill sid thread
其中：
sid：表示要杀死的进程属于的实例名
thread：是要杀掉的线程号，即第3步查询出的spid。
例：c:>orakill orcl 12345

ORA-00031: session marked for kill

Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptible operation is done.

Action: No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner.

 

kill -9 12345

ps：查找被锁的pkg

SELECT pkg.NAME,

       pkg.owner,

       acc.sid,

       ses.SERIAL#,

       ses.PADDR,

       prc.spid,

       ses.MACHINE,

       'ALTER SYSTEM KILL SESSION ''' || SES.SID || ',' || SES.SERIAL# ||

       ''';'

  FROM V$DB_OBJECT_CACHE pkg, v$access acc, v$session ses, v$process prc

 WHERE pkg.owner = acc.owner

   AND pkg.NAME = acc.OBJECT

   AND acc.SID = ses.SID

   AND prc.addr = ses.paddr

   AND pkg.OWNER = 'APPS'

   AND pkg.LOCKS != '0'

   AND pkg.NAME LIKE 'BMC%';

查被锁的对象：

SELECT DOB.OBJECT_NAME TABLE_NAME,

       LO.SESSION_ID,

       VSS.SERIAL#,

       VSS.ACTION ACTION,

       VSS.OSUSER OSUSER,

       VSS.PROCESS AP_PID,

       VPS.SPID DB_PID,

       'ALTER SYSTEM KILL SESSION ''' || VSS.SID || ',' || VSS.SERIAL# ||

       ''';'

  FROM V$LOCKED_OBJECT LO, DBA_OBJECTS DOB, V$SESSION VSS, V$PROCESS VPS

 WHERE LO.OBJECT_ID = DOB.OBJECT_ID

   AND LO.SESSION_ID = VSS.SID

   AND VSS.PADDR = VPS.ADDR

 ORDER BY 2, 3, DOB.OBJECT_NAME;

 

追请求锁:

Select r.Rowid

  From Fnd_Concurrent_Requests r

 Where r.Hold_Flag = 'N'

   And r.Status_Code = 'I'

   And r.Requested_Start_Date <= Sysdate

   And ((r.Ops_Instance Is Null) Or (r.Ops_Instance = -1) Or (r.Ops_Instance = Fnd_Conc_Global.Ops_Inst_Num))

   And (r.Node_Name1 Is Null Or (r.Node_Name1 Is Not Null And Fnd_Dcp.Target_Node_Mgr_Chk(r.Request_Id) = 1))

   And Exists

 (Select Null

          From Fnd_Concurrent_Programs p

         Where p.Enabled_Flag = 'Y'

           And r.Program_Application_Id = p.Application_Id

           And r.Concurrent_Program_Id = p.Concurrent_Program_Id

           And Exists (Select Null

                  From Fnd_Oracle_Userid o

                 Where r.Oracle_Id = o.Oracle_Id

                   And Exists (Select Null

                          From Fnd_Conflicts_Domain c

                         Where p.Run_Alone_Flag = c.Runalone_Flag

                           And r.Cd_Id = c.Cd_Id))

           And (p.Execution_Method_Code != 'S' Or (r.Program_Application_Id, r.Concurrent_Program_Id) In

               ((0, 98), (0, 100), (0, 31721), (0, 31722), (0, 31757)))) And ((r.Program_Application_Id, r.Concurrent_Program_Id) Not In((101,

                                                                                                                                          60729),

                                                                                                                                    (140,

                                                                                                                                          39446),

                                                                                                                                         (200,

                                                                                                                                          20567),

                                                                                                                                         (200,

                                                                                                                                          37447))

 

SELECT EVENT, SID, P1, P2, P3

   FROM V$SESSION_WAIT

  WHERE EVENT LIKE 'enq:%';

 SELECT ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#

   FROM V$SESSION

  WHERE EVENT = 'enq: TX - row lock contention';

 SELECT * FROM DBA_OBJECTS DO WHERE DO.OBJECT_ID = '5667076';

 SELECT SID, SQL_TEXT

   FROM V$SESSION A, V$SQL B

  WHERE SID = 3413

AND (B.SQL_ID = A.SQL_ID OR B.SQL_ID = A.PREV_SQL_ID);

 

ORA-60 DEADLOCK DUE TO BITMAP INDEX IN RAC (文档 ID 1496403.1)