ТЕМА:

SELECT (SELECT username
          FROM v$session
         WHERE sid = a.sid) AS "Кто блокирует",
       a.sid,  ' блокирует ',
      (SELECT username  FROM v$session WHERE sid = b.sid) AS "Кто заблокирован",
       b.sid
  FROM v$lock a, v$lock b
 WHERE a.BLOCK = 1
       AND b.request > 0
       AND a.id1 = b.id1
       AND a.id2 = b.id2

set linesize 140
set pagesize 100
clear column
col sid format a15
col serial format a10
col os_pid format a10
col username format a12
col osuser format a10
col program format a16
col status format a8
col event format a20
col sql_id format a15
select bl.blocking_tree "SID"
,      to_char(ps.serial#) "SERIAL"
,      ps.os_pid
,      ps.username
,      ps.osuser
,      ps.program
,      ps.status
,      ps.event
,      ps.sql_id
from
(select rownum rn, blocking_tree,locked from (with locks as (select blocking_session locker , sid locked from v$session
     where blocking_session is not null)
SELECT LPAD(locked, LENGTH(locked)+(level*2-2), ' ') blocking_tree, locked
FROM   (SELECT * FROM locks
        UNION
        SELECT NULL, locker
        FROM locks
        WHERE locker NOT IN (SELECT locked FROM locks))
CONNECT BY PRIOR locked = locker
START WITH locker IS NULL)) bl,
(select  s.sid
,       s.serial#
,       s.username
,       s.osuser
,       p.spid "OS_PID"
,       s.program
,       s.status
,       s.event
,       s.sql_id
from    v$session s
,       v$process p
Where   s.paddr = p.addr
order   by status,username) ps
where bl.locked=ps.sid
order by bl.rn