This script shows the ‘waiters’ and the ‘lockers’ per instance. Used it a lot.

set pagesize 10000
set echo off
set feedback on
set linesize 500
set recsep off

column h_user format a11
column h_mach format a6
column h_stat format a6
column h_inst format a6
column h_sid format 9999
column h_object format a30
column h_mode format a9
column w_inst format a6
column w_sid format 9999
column w_srt_lock format a21
column w_srt_req format a13
column w_user format a11

SELECT s.username h_user
, substr(s.machine,9,14) h_mach
, substr(s.status,1,1) h_stat
, decode(lh.inst_id
,”) h_inst
, lh.sid h_sid
, decode(lh.lmode, 2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S/Row-X (SSX)’,
6, ‘Exclusive’) h_mode
, decode(lw.inst_id
, ”) w_inst
, lw.sid w_Sid
, decode ( lh.type,
‘MR’, ‘Media_recovery’,
‘RT’, ‘Redo_thread’,
‘UN’, ‘User_name’,
‘TX’, ‘Transaction’,
‘TM’, ‘Dml’,
‘UL’, ‘PLSQL User_lock’,
‘DX’, ‘Distrted_Transaxion’,
‘CF’, ‘Control_file’,
‘IS’, ‘Instance_state’,
‘FS’, ‘File_set’,
‘IR’, ‘Instance_recovery’,
‘ST’, ‘Diskspace Transaction’,
‘IV’, ‘Libcache_invalidation’,
‘LS’, ‘LogStaartORswitch’,
‘RW’, ‘Row_wait’,
‘SQ’, ‘Sequence_no’,
‘TE’, ‘Extend_table’,
‘TT’, ‘Temp_table’,
‘Nothing-‘ ) w_srt_lock
, decode ( lw.request,
0, ‘None’,
1, ‘NoLock’,
2, ‘Row-Share’,
3, ‘Row-Exclusive’,
4, ‘Share-Table’,
5, ‘Share-Row-Exclusive’,
6, ‘Exclusive’,
‘Nothing-‘ ) w_srt_req
FROM gv$lock lw
, gv$lock lh
, gv$session s
WHERE lh.id1 = lw.id1
AND lh.id2 = lw.id2
AND lh.request= 0
AND lw.lmode= 0
AND lh.sid = s.sid
AND lh.inst_id = s.inst_id
AND (lh.id1,lh.id2) in
SELECT id1,id2 FROM gv$lock WHERE request=0
SELECT id1,id2 FROM gv$lock WHERE lmode=0