法一、
1、建立相关数据字典
$ORACLE_HOME/rdbms/admin/catblock.sql
2、查询dba_blockers及dba_waiters找到阻塞或wait进程。
select * from dba_blockers
select * from dba_waiters
3、杀掉相关的session
法二、
1、直接用如下语句查找锁定object的session
SELECT /*+ ordered */
W1.SID WAITING_SESSION,
H1.SID HOLDING_SESSION,
W.KGLLKTYPE LOCK_OR_PIN,
W.KGLLKHDL ADDRESS,
DECODE(H.KGLLKMOD,
0,
'None',
1,
'Null',
2,
'Share',
3,
'Exclusive',
'Unknown') MODE_HELD,
DECODE(W.KGLLKREQ,
0,
'None',
1,
'Null',
2,
'Share',
3,
'Exclusive',
'Unknown') MODE_REQUESTED
FROM DBA_KGLLOCK W, DBA_KGLLOCK H, V$SESSION W1, V$SESSION H1
WHERE (((H.KGLLKMOD != 0) AND (H.KGLLKMOD != 1) AND
((H.KGLLKREQ = 0) OR (H.KGLLKREQ = 1))) AND
(((W.KGLLKMOD = 0) OR (W.KGLLKMOD = 1)) AND
((W.KGLLKREQ != 0) AND (W.KGLLKREQ != 1))))
AND W.KGLLKTYPE = H.KGLLKTYPE
AND W.KGLLKHDL = H.KGLLKHDL
AND W.KGLLKUSE = W1.SADDR
AND H.KGLLKUSE = H1.SADDR
2、杀掉相关的session
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/717880/viewspace-821973/ ，如需转载，请注明出处，否则将追究法律责任。
