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