ORACLE ORA-04021:等待锁定对象XX时发生超时 杀死引起锁定的会话

今天遇到一个问题:在编译存储过程时开发工具卡死.

经验证明这是其它会话在执行该过程.

查询是否该过程在执行:

select a.SID,a.SERIAL#,b.SQL_FULLTEXT,a.MACHINE,a.STATUS,a.BLOCKING_SESSION,a.BLOCKING_SESSION_STATUS

from v$session a, v$sql b

where a.SQL_ID = b.SQL_ID

and a.USERNAME = 'KDBASE';

1 8 13857 <CLOB> WORKGROUP\WIN-EVS47DI7BMP ACTIVE NO HOLDER

2 67 3984 <CLOB> WORKGROUP\WIN-EVS47DI7BMP INACTIVE NO HOLDER

3 67 3984 <CLOB> WORKGROUP\WIN-EVS47DI7BMP INACTIVE NO HOLDER

4 72 5588 <CLOB> WORKGROUP\LCJ-PC ACTIVE UNKNOWN

5 83 22191 <CLOB> WORKGROUP\LCJ-PC ACTIVE UNKNOWN

6 227 8739 <CLOB> WORKGROUP\LCJ-PC ACTIVE UNKNOWN

发现在会话72,5588正在执行

BEGIN UP_MID_88011113( :1, :2, :3, :4, :5, :6, :7, :8, :RC1, :RC2 ); END;

找到原因,杀死会话

alter system kill session '72,5588';

杀死后,再查询,发现会话状态己改了

1 72 5588 <CLOB> WORKGROUP\LCJ-PC KILLED UNKNOWN

由ACTIVE改成KILLED了.

但是依然无法编译这个存储过程.说明kill session只是把会话的状态改了一下而已,会话使用的资源依然没有释放.

alter system kill session命令无效,现在要用orakill来做了.

orakill是ORACLE用来杀死线程的工具.

 

现在来查询一下线程号:

SELECT s.sid AS "Sid",

s.serial# AS "Serial#",

p.spid AS "ThreadID",

s.osuser AS "OSUser",

s.program AS "Program"

FROM v$process p, v$session s

WHERE p.addr = s.paddr(+)

and s.SID=72;

1 72 5588 8844 LCJ kcbpas.exe

操作系统线程号是5588,用ORAKILL来杀吧:

ORAKILL用法:

Usage:  orakill sid thread

快来试试吧:

C:\Users\Administrator>orakill orcl 8844

Kill of thread id  8844   in instance orcl successfully signalled.

 

再执行一遍查询:

SQL> SELECT s.sid AS "Sid",

2 s.serial# AS "Serial#",

3 p.spid AS "ThreadID",

4 s.osuser AS "OSUser",

5 s.program AS "Program"

6 FROM v$process p, v$session s

7 WHERE p.addr = s.paddr(+)

8 and s.SID=72

9 /

Sid Serial# ThreadID OSUser Program

---------- ---------- ------------ ------------------------------ ----------------------------------------------------------------

没查到数据,说明该线程已经被杀死了.

再来编译一下存储过程.

依然是ORA-04021:等待锁定对象XX时发生超时!!怎么回事!

 

原来,执行KILL SESSION 命令后,会改变会话的PADDR,就是进程地址,指向一个虚拟的地址.请参考:

http://www.eygle.com/faq/Kill_Session.htm

 

查询会话原来的PADDR:

SELECT s.sid AS "Sid",

s.serial# AS "Serial#",

p.spid AS "ThreadID",

p.ADDR,

p.USERNAME,

p.TERMINAL,

p.PROGRAM

FROM v$process p, v$session s

WHERE p.addr = s.paddr(+)

and p.PID<>1

and s.SID is null

1 8532 000007FF38006A80 SYSTEM WINDOWS-E4VC1PI ORACLE.EXE (D000)

2 5384 000007FF380072A8 SYSTEM WINDOWS-E4VC1PI ORACLE.EXE (S000)

杀掉这两个进程

orakill orcl 8532

orakill orcl 5384

存储过程依然不能编译,说明用这种方法查询的是有问题.

而且测试过程中,用上面个查询查到的线程号是会变的,也就是说用执行上面两个ORAKILL后,再次执行查询,会查出来两条其它的线程!!

 

看来要用ORAKILL,在执行kill session命令之前要先查出会话的paddr,然后执行kill session命令,如果kill session不奏效,再通过刚才查出来的paddr查v$process表,找到线程号.有环境一定要实验一下.