set line 125
set pages 1000
column SID format 9999
column SERIAL# format 99999
column TABEL format A25
column SPID format A5
column PROGRAM format A17
column LOCK_MODE format A12
column MACHINE format A20
column sql_text form a124

select sid, serial#, spid, status, logon_time, tabel, program, machine, lock_mode, sql_text||chr(10)||rpad('-',124,'-') sql_text from (
select s.sid, s.serial#, p.spid, s.status, s.logon_time, o.object_name TABEL, s.program, s.machine,
decode (s.lockwait, null, 'Have Lock(s)', 'Waiting for <' || b.sid || '>') LOCK_MODE, c.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$sql c
where l.sid = s.sid
and o.object_id (+) = l.id1
and c.sql_id(+) = s.sql_id
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, s.program, s.machine, p.spid, s.process, s.sid, s.serial#, s.status, s.logon_time, decode (s.lockwait, null, 'Have Lock(s)',
'Waiting for <' || b.sid || '>'), c.sql_text
order by
decode (s.lockwait, null, 'Have Lock(s)', 'Waiting for <' || b.sid || '>') desc, o.object_name asc, s.sid asc)
/

Labels: Oracle