Oracle DBA Script : Monitor and veryfy deadlocks

Monitor and Verify DEAD Locks: Holding and Waiting Sessions

Code: 

set lines 80 echo on ver off timing on term on pages 60 feed on head on
spool DEAD_LOCK_WAITERS.LST

col " " for A25
col "Holding Session Info" for A25
col "Waiting Session Info" for A25


select --+ ORDERED
'Session ID' || CHR(10) ||
'Mode Held' || CHR(10) ||
'Lock Type' || CHR(10) ||
'Mode Requested' || CHR(10) ||
'Lock ID 1' || CHR(10) ||
'Lock ID 2' " "
-------------------------------- END of Header
,
HH.session_id || CHR(10) ||
HH.mode_held || CHR(10) ||
HH.lock_type || CHR(10) ||
HH.mode_requested || CHR(10) ||
HH.lock_id1 || CHR(10) ||
HH.lock_id2 "Holding Session Info"
------------------------------ END of Holding Session
,
Ww.session_id || CHR(10) ||
WW.mode_held || CHR(10) ||
Ww.lock_type || CHR(10) ||
Ww.mode_requested || CHR(10) ||
Ww.lock_id1 || CHR(10) ||
Ww.lock_id2 "Waiting Session Info"
------------------------------ END of Waiting Session  from ----------------
(
select /*+ RULE */ *
from SYS.dba_locks
where blocking_others = 'Blocking' and
mode_held != 'None' and
mode_held != 'Null'
) HH,
-----------------------------------------------------
(
select /*+ RULE */ *
from SYS.dba_locks
where mode_requested != 'None'
) WW
-----------------------------------------------------
where WW.lock_type = HH.lock_type and
WW.lock_id1 = HH.lock_id1 and
WW.lock_id2 = HH.lock_id2
;

spool off