Sunday, June 21, 2015

Find blocking query - Oracle

If you have a query that stops and it seems that it will never end, you might have another query blocking it. Here's one help to find out which query blocking it.

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 l1.id2 = l2.id2;


References:
http://www.oraclerecipes.com/monitoring/find-blocking-sessions/