Deteksi Lock

set linesize 150
set pages 100

col name for a21 head "Locked Object"
col session_id for 99999 head SID
col serial# for 99999 head SER#
col oracle_username for a12 head "Locking User"
col lock_type for a12 head "Lock Type"
col mode_held for a12 head "Mode Held"
col event for a30

SELECT a.session_id, b.serial#, a.oracle_username, c.name,
decode(d.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distrib Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
d.type) lock_type,
decode(d.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'Null', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(d.lmode)) mode_held,
e.event, e.SECONDS_IN_WAIT "Wait(Seconds)"
FROM sys.obj$ c, v$session b, v$locked_object a,
sys.v_$lock d, v$session_wait e
WHERE a.session_id=b.sid
AND b.sid=e.sid
AND c.obj#=a.object_id
AND a.object_id=d.id1
AND b.sid=d.sid
order by e.SECONDS_IN_WAIT desc
;