SQL for querying database locks

Select 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",
Sq.sql_fulltext "SQL",
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 other processes */
2, ' Global ',/**//* this lock are 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,v$sql sq
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 ')
and WS. Sql_address=sq.address
and WS. Sql_hash_value=sq.hash_value

SQL for querying database locks