ORA-04021: 等待对象锁超时

很多时候Oracle存储过程在跑，不巧编译了它，就会产生ORA-04021: 等待对象锁超时的错误。session1:
create or replace procedure p_test is
begin
dbms_lock.sleep（1000）;
end;

call p_test（）;

session2:
alter procedure p_test compile;

session3:
select * from dba_ddl_locks where name="P_TEST";
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU
---------- ------ ------- -------- --------------------- --------- -------
191 TEST P_TEST Table/Procedure/Type Null None
132 TEST P_TEST Table/Procedure/Type Exclusive None
select s.SID,s.SERIAL# from v$session s where s.sid=191;
SID SERIAL#
---------- ----------
191 14
alter system kill session "191,14";
alter system kill session "191,14"
*
第 1 行出现错误:
ORA-00031: 标记要终止的会话

SQL> select spid
from gv$process p, gv$session s
where p.INST_ID = s.INST_ID
and p.INST_ID = 1
and s.SID = 191
and s.SERIAL# = 14
and p.ADDR = s.PADDR;
SPID
------------------------
7484

linux:kill -9 7484
windows:orakill ora11 7484

也可以通过来验证
select a.INST_ID,
"alter system kill session " || """" || b.sid || "," || b.SERIAL# ||
""";" kill_command
from gV$ACCESS a, gV$session b
where a.type = "PROCEDURE"
and a.OBJECT in （"P_TEST"）
and a.sid = b.sid
and a.INST_ID = b.INST_ID;

select a.INST_ID,"kill -9 "||p.SPID
from gV$ACCESS a, gV$session b,gv$process p
where a.type = "PROCEDURE"
and a.OBJECT in （"P_TEST"）
and a.sid = b.sid
and p.ADDR = b.PADDR
and a.INST_ID = b.INST_ID
and b.INST_ID = p.INST_ID; 更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址