The script can look at now who is locking the table and who is waiting for the table.
--
--Find out who is locking the table now, and who is waiting for the table.
--Lock.sql
--Purplefox
--
SELECT/*+ Choose * *
Bs.username "Blocking user", bs.username "DB user",
Ws.username "Waiting User", bs.sid "Sid", Ws.sid "Wsid",
bs.serial# "serial#", bs.sql_address "address",
Bs.sql_hash_value "SQL Hash", bs.program "Blocking App",
Ws.program "Waiting App", bs.machine "Blocking machine",
Ws.machine "Waiting Machine", Bs.osuser "Blocking OS User",
Ws.osuser "Waiting OS User", bs.serial# "serial#",
ws.serial# "wserial#",
DECODE (
Wk. TYPE,
' MR ', ' Media Recovery ',
' RT ', ' Redo Thread ',
' UN ', ' USER Name ',
' TX ', ' Transaction ',
' TM ', ' DML ',
' UL ', ' pl/sql USER LOCK ',
' DX ', ' Distributed 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 ',
Wk. TYPE
) Lock_type,
DECODE (
Hk.lmode,
0, ' None ',
1, ' NULL ',
2, ' Row-s (SS) ',
3, ' Row-x (SX) ',
4, ' SHARE ',
5, ' S/row-x (SSX) ',
6, ' EXCLUSIVE ',
To_char (Hk.lmode)
) Mode_held,
DECODE (
Wk.request,
0, ' None ',
1, ' NULL ',
2, ' Row-s (SS) ',
3, ' Row-x (SX) ',
4, ' SHARE ',
5, ' S/row-x (SSX) ',
6, ' EXCLUSIVE ',
To_char (Wk.request)
) mode_requested,
To_char (HK.ID1) lock_id1, To_char (HK.ID2) Lock_id2,
DECODE (
hk. block,
0, ' not Blocking ',//not Blocking any other processes *
1, ' Blocking ',/* This lock blocks the other processes *
2, ' global ',/* This lock's global, so we can ' t-tell * *
To_char (HK. Block)
)
Blocking_others
From V$lock HK, v$session BS, V$lock wk, v$session ws
WHERE HK. Block = 1
and Hk.lmode!= 0
and Hk.lmode!= 1
and wk.request!= 0
and WK. TYPE (+) = HK. TYPE
and WK.ID1 (+) = Hk.id1
and Wk.id2 (+) = Hk.id2
and Hk.sid = Bs.sid (+)
and Wk.sid = Ws.sid (+)
and (Bs.username is not NULL)
and (Bs.username <> ' SYSTEM ')
and (Bs.username <> ' SYS ')
ORDER BY 1
/