1、首先确定执行此存储过程的会话,o.kglnaobj即为会话锁住的存储过程名称:
Select distinct s.sid ,s.SERIAL#, username ,o.kglnaobj
from v$session s , sys.x$kglob o , sys.x$kglpn p where p.kglpnhdl=o.kglhdadr and s.saddr=p.kglpnuse ;
2、查看被堵塞的sql语句,SID为1中查到的SID
select sid,SERIAL#,a.EVENT, a.SQL_ID, a.MACHINE, b.SQL_TEXT, b.SQL_FULLTEXT,b.FIRST_LOAD_TIME,b.LAST_LOAD_TIME,b.LAST_ACTIVE_TIME
from v$session a, v$sql b
where a.SQL_ID = b.SQL_ID
and a.USERNAME is not null
and a.STATUS = 'ACTIVE' and SID = '#SID'
3、杀掉会话,#SID和#SERIAL是从2查到的
alter system kill session '#SID,#SERIAL';
4、如果清理不掉,需要到服务器上面去杀掉进程
查询进程id
select spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=#SID
清理进程
kill -9 spid(是上一句sql查询出来的)