1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
|
select
s.sid "SID",
s.serial# "SER",
o.object_name "Table",
o.owner,
s.osuser "OS User",
s.machine "Node",
s.terminal "Terminal",
--p.spid "SPID",
--s.process "CPID",
decode (s.lockwait, null, 'Have Lock(s)', 'Waiting for <' || b.sid || '>') "Mode",
substr (c.sql_text, 1, 150) "SQL Text"
from v$lock l,
v$lock d,
v$session s,
v$session b,
v$process p,
v$transaction t,
sys.dba_objects o,
v$open_cursor c
where l.sid = s.sid
and o.object_id (+) = l.id1
and c.hash_value (+) = s.sql_hash_value
and c.address (+) = s.sql_address
and s.paddr = p.addr
and d.kaddr (+) = s.lockwait
and d.id2 = t.xidsqn (+)
and b.taddr (+) = t.addr
and l.type = 'TM'
group by
o.object_name,
o.owner,
s.osuser,
s.machine,
s.terminal,
p.spid,
s.process,
s.sid,
s.serial#,
decode (s.lockwait, null, 'Have Lock(s)', 'Waiting for <' || b.sid || '>'),
substr (c.sql_text, 1, 150)
order by
decode (s.lockwait, null, 'Have Lock(s)', 'Waiting for <' || b.sid || '>') desc,
o.object_name asc,
s.sid asc |
Partager