ORA-04021: 等待对象锁超时

106次阅读

共计 2937 个字符，预计需要花费 8 分钟才能阅读完成。

很多时候 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;