SELECT distinct w.tm, w.p2 OBJECT_ID, l.inst_id, l.sid, l.lmode, l.request,l.blocklmode, l.request,l.block
FROM
2 3 ( SELECT p2, p3, 'TM-'||substr(p2raw,-8)||'-'||lpad(p3,8,'0') TM
4 FROM v$session_wait
5 WHERE event='enq: TM - contention'
6 and state='WAITING'
7 ) W,
8 gv$lock L
9 WHERE l.type(+)='TM'
10 and l.id1(+)=w.p2
11 and l.id2(+)=w.p3
12 ORDER BY tm, lmode desc, request desc
13 ;
TM OBJECT_ID INST_ID SID LMODE REQUEST BLOCK
-------------------- ---------- ---------- ---------- ---------- ---------- ----------
TM-00013866-00000000 79974 1 113 6 0 1
TM-00013866-00000000 79974 1 91 0 3 0