Thursday, 10 November 2011

Query to find Blocking Sessions

Below is the query which will give the details about the blocking sessions

SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess,
       id1, id2, lmode, request, type, inst_id
FROM GV$LOCK
WHERE (id1, id2, type) IN
   (SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
     ORDER BY id1, request;

GV$LOCK is for RAC else we can replace with V$LOCK.

1 comment: