Erwfr

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",
BS.SQL_HASH_VALUE "Sql hash",
BS.PROGRAM "Blocking App",
WS.PROGRAM "Waiting App",
BS.MACHINE "Blocking Machine",
WS.MACHINE "WaitingMachine",
BS.OSUSER "Blocking OS User",
WS.OSUSER "Waiting OS User",
BS.SERIAL# "Serial#",
WS.SERIAL# "WSerial#",
DECODE(WK.TYPE,'MR','MediaRecovery','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','InstanceRecovery','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','TempTABLE',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,'NOTBlocking', /* Not blocking any other processes */ 1,'Blocking', /* This lock blocks other processes */2,'Global', /* This lock is global, so we can't tell */TO_CHAR(HK.BLOCK)) BLOCKING_OTHERS
FROMV$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.ID1AND 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