Thursday, September 24, 2009

Friday, September 11, 2009

Who's blocking who on what

The following queries are based on this excellent post.
 
/* Who's blocking who */
SELECT s1.username ||
       '@' ||
       s1.machine ||
       ' ( SID=' ||
       s1.SID ||
       ' )  is blocking ' ||
       s2.username ||
       '@' ||
       s2.machine ||
       ' ( SID=' ||
       s2.SID ||
       ' ) ' AS blocking_status
FROM   v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE  s1.SID = l1.SID
AND    s2.SID = l2.SID
AND    l1.BLOCK = 1
AND    l2.request > 0
AND    l1.id1 = l2.id1
AND    l2.id2 = l2.id2;


/* Which object and row is blocked */
SELECT SID, DO.object_name, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
       DBMS_ROWID.rowid_create (1, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#)
FROM   v$session s, dba_objects DO
WHERE  s.row_wait_obj# = DO.object_id
AND    SID IN (
           SELECT DISTINCT s2.SID
           FROM            v$lock l1, v$session s1, v$lock l2, v$session s2
           WHERE           s1.SID = l1.SID
           AND             s2.SID = l2.SID
           AND             l1.BLOCK = 1
           AND             l2.request > 0
           AND             l1.id1 = l2.id1
           AND             l2.id2 = l2.id2);