select /*+ RULE */ B.INST_ID, B.SID, C.USERNAME, C.OSUSER,
       DECODE(B.ID2, 0, A.OBJECT_NAME,
                    'Trans-'||to_char(B.ID1)) OBJECT_NAME,
       B.TYPE,
       DECODE(B.LMODE,0,'--Waiting--',
                      1,'Null',
                      2,'Row Share',
                      3,'Row Excl',
                      4,'Share',
                      5,'Sha Row Exc',
                      6,'Exclusive',
                      'Other') "Lock Mode",
       DECODE(B.REQUEST,0,' ',
                        1,'Null',
                        2,'Row Share',
                        3,'Row Excl',
                        4,'Share',
                        5,'Sha Row Exc',
                        6,'Exclusive',
                       'Other') "Req Mode"
from   DBA_OBJECTS A,
       GV$LOCK      B, 
       GV$SESSION C
where  B.INST_ID=C.INST_ID
  and  A.OBJECT_ID(+) = B.ID1
  and  B.SID = C.SID
  and  C.USERNAME is not null
order by B.SID, B.ID2
/
