SELECT /*+ rule */
a.Sid,
b.Serial#,
Decode(a.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 Invalida-tion',
'LS',
'Log Start or Switch',
'RW',
'Row Wait',
'SQ',
'Sequence Number',
'TE',
'Extend Table',
'TT',
'Temp Table',
'Unknown') Locktype,
c.Object_Name,
---b.username,
---b.osuser,
Decode(a.Lmode,
0,
'None',
1,
'Null',
2,
'Row-S',
3,
'Row-X',
4,
'Share',
5,
'S/Row-X',
6,
'Exclusive',
'Unknown') Lockmode,
b.Machine,
d.Spid
FROM V$lock a, V$session b, All_Objects c, V$process d
WHERE a.Sid = b.Sid
AND a.TYPE IN ('TM', 'TX')
AND c.Object_Id = a.Id1
AND b.Paddr = d.Addr;
