Blocking locks

SET LINESIZE  300
SET PAGESIZE  9999
SET VERIFY    off

SELECT
    SUBSTR(s1.username,1,12)           "WAITING USER"
  , SUBSTR(s1.osuser,1,8)              "OS User"
  , SUBSTR(TO_CHAR(w.session_id),1,5)  "Sid"
  , p1.spid                            "PID"
  , SUBSTR(s2.username,1,12)           "HOLDING User"
  , SUBSTR(s2.osuser,1,8)              "OS User"
  , SUBSTR(TO_CHAR(h.session_id),1,5)  "Sid"
  , p2.spid                            "PID"
FROM
    sys.v_$process p1
  , sys.v_$process p2
  , sys.v_$session s1
  , sys.v_$session s2
  , dba_locks  w
  , dba_locks  h
WHERE
      h.mode_held      != 'None'
  AND h.mode_held      != 'Null'
  AND w.mode_requested != 'None'
  AND w.lock_type  (+)  = h.lock_type
  AND w.lock_id1   (+)  = h.lock_id1
  AND w.lock_id2   (+)  = h.lock_id2
  AND w.session_id      = s1.sid   (+)
  AND h.session_id      = s2.sid   (+)
  AND s1.paddr          = p1.addr  (+)
  AND s2.paddr          = p2.addr  (+)
/

Blocking locks (RAC)

set linesize 500
set pagesize 9999
select  /*+ ordered */ 
		to_char(l.sid)|| ' - ' || to_char(w.sid) SID,
		'DB instance: ' || trim(i.instance_name) || ' : blocking lock : ' ||trim(w.event) ||' - '
        ||' WAITER: dbuser='|| ltrim(s.username)
       ||' sid='|| rtrim(ltrim(w.sid))
       ||' ospid='|| rtrim(ltrim(p.spid))
       ||' time_waited(s)='|| rtrim(ltrim(to_char(w.seconds_in_wait)))
       ||' program='|| rtrim(ltrim(s.program))
       ||' machine='|| rtrim(ltrim(s.machine))
       ||' - BLOCKER: dbuser='|| ltrim(s1.username)
       ||' sid='|| rtrim(ltrim(l.sid))
       ||' ospid='|| rtrim(ltrim(p1.spid))
       ||' program='|| rtrim(ltrim(s1.program))
       ||' machine='|| rtrim(ltrim(s1.machine)) lock_desc,
	   w.seconds_in_wait
from gv$lock l, v$session s, v$process p, v$session_wait w,
     gv$session s1, gv$process p1, v$instance i
where ( w.event = 'enqueue' or w.event like 'enq%' )
and w.state = 'WAITING'
and w.p2 = l.id1
and l.block in (1, 2)
and upper(l.type) in ('TM','TX','UL')
and w.sid = s.sid
and s.paddr = p.addr
and l.sid = s1.sid
and l.inst_id  = s1.inst_id
and s1.paddr = p1.addr
and s1.inst_id = p1.inst_id
order by s.sid, s.serial#;

Blocking session

select
    blocking_session,
    sid,    serial#,
    wait_class,
    seconds_in_wait 
from
    v$session 
where
    blocking_session is not NULL
order by
    blocking_session
/