SELECT /*+ rule */ s.sid, s.serial#,
l.type, l.id1, l.id2, l.lmode, l.request,
l.ADDR, l.KADDR, l.CTIME, l.BLOCK,
S.USERNAME, S.OSUSER, S.MACHINE,
DECODE(L.ID2, 0, to_char(o.owner#)||'-'||O.name,
'Trans-'||to_char(L.ID1)||'-'||L.ID2) OBJECT_NAME,
DECODE(L.LMODE,0,'--Waiting--',
1,'Null',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Sha Row Exc',
6,'Exclusive',
'Other') Lock_Mode,
DECODE(L.REQUEST,0,' ',
1,'Null',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Sha Row Exc',
6,'Exclusive',
'Other') Req_Mode
FROM v$lock l, v$session s, sys.obj$ o
where l.request = 0
and l.sid = s.sid
and l.ID1 = o.OBJ#(+)
and s.USERNAME is not null
order by S.USERNAME, L.SID, L.BLOCK;
|
