SELECT
ih.instance_name || ' - ' || lh.sid locking_instance
, iw.instance_name || ' - ' || lw.sid waiting_instance
, DECODE ( lh.type
, 'CF', 'Control File'
, 'DX', 'Distrted Transaction'
, 'FS', 'File Set'
, 'IR', 'Instance Recovery'
, 'IS', 'Instance State'
, 'IV', 'Libcache Invalidation'
, 'LS', 'LogStartORswitch'
, 'MR', 'Media Recovery'
, 'RT', 'Redo Thread'
, 'RW', 'Row Wait'
, 'SQ', 'Sequence #'
, 'ST', 'Diskspace Transaction'
, 'TE', 'Extend Table'
, 'TT', 'Temp Table'
, 'TX', 'Transaction'
, 'TM', 'Dml'
, 'UL', 'PLSQL User_lock'
, 'UN', 'User Name'
, 'Nothing-'
) waiter_lock_type
, DECODE ( lw.request
, 0, 'None'
, 1, 'NoLock'
, 2, 'Row-Share'
, 3, 'Row-Exclusive'
, 4, 'Share-Table'
, 5, 'Share-Row-Exclusive'
, 6, 'Exclusive'
, 'Nothing-'
) waiter_mode_req
FROM
gv$lock lw
, gv$lock lh
, gv$instance iw
, gv$instance ih
WHERE
iw.inst_id = lw.inst_id
AND ih.inst_id = lh.inst_id
AND lh.id1 = lw.id1
AND lh.id2 = lw.id2
AND lh.request = 0
AND lw.lmode = 0
AND (lh.id1, lh.id2) IN ( SELECT id1,id2
FROM gv$lock
WHERE request = 0
INTERSECT
SELECT id1,id2
FROM gv$lock
WHERE lmode = 0
)
ORDER BY
lh.sid
/
anargodjaev.wordpress.com 