Showing posts with label Lock detection. Show all posts
Showing posts with label Lock detection. Show all posts

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);