SELECT /*+ NOPARALLEL */ DECODE(l.request,0,'Holder:',' Waiter:') blocker, lpad(' ',DECODE(l.request,0,0,1))||l.sid sid,
s.serial#, l.inst_id,s.machine,s.event, to_char(trunc(sysdate) + (l.ctime/24/60/60), 'HH24:MI:SS') seconds_in_wait,
s.status, s.logon_time, s.sql_hash_value,
DECODE(l.request,0,'',' ')||decode(s.sql_id, null, s.prev_sql_id, s.sql_id) sql_id,
decode(s.command,
'0','NO',
'1','Create Tab',
'2','Insert',
'3','Select',
'6','Update',
'7','Delete',
'9','Create Idx',
'10','Drop Idx',
'15','Alter Tbl',
'24','Create Proc',
'32','Create Link',
'33','Drop Link',
'36','Create RBS',
'37','Alter RBS',
'38','Drop RBS',
'40','Alter TBS',
'41','Drop TBS',
'42','Alter Sess',
'45','Rollback',
'47','PL/SQL Exe',
'62','Anal Table',
'63','Anal Index',
'85','Truncate') Command,
decode(l.lmode,
0,'(0)None',
1,'(1)Null',
2,'(2)Row Share',
3,'(3)Row Exclu',
4,'(4)Share',
5,'(5)Share Row Ex',
6,'(6)Exclusive') lmode,
decode(l.request,
0,'(0)None',
1,'(1)Null',
2,'(2)Row Share',
3,'(3)Row Exclu',
4,'(4)Share',
5,'(5)Share Row Ex',
6,'(6)Exclusive') request,
l.type, l.block
FROM gv$session s, gv$lock l
WHERE EXISTS (SELECT 1 FROM gv$lock o WHERE o.lmode = 0 and o.id1 = l.id1 and o.id2 = l.id2 and o.type in ('TM','TX'))
and l.inst_id = s.inst_id
and l.sid = s.sid
ORDER BY l.id1,l.request;