set linesize 200 feedback off heading on
column sid format 9999
column res heading 'Resource Type' format a20
column id1 format 9999999
column id2 format 9999999
column lmode heading 'Lock Held' format a14
column request heading 'Lock Req.' format a14
column serial# format 99999
column username format a12
column tab format a20
column owner format a8
select '''' || l.sid || ',' || s.serial# || '''' "SID/Serial",
s.username,
decode(l.type,'RW','RW - Row Wait Enqueue',
'TM','TM - DML Enqueue',
'TX','TX - Trans Enqueue',
'UL','UL - User',l.type||'System') res,
u.name owner, substr(t.name,1,20) tab,
l.id1,l.id2,
decode(l.lmode,1,'No Lock',
2,'Row Share',
3,'Row Exclusive',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',null) lmode,
decode(l.request,1,'No Lock',
2,'Row Share',
3,'Row Excl',
4,'Share',
5,'Shr Row Excl',
6,'Exclusive',null) request
from v$newlock l, v$session s,
sys.user$ u,sys.obj$ t
where l.sid = s.sid
and s.type != 'BACKGROUND'
and t.obj# = l.id1
and u.user# = t.owner#
order by username
/
set feedback on
