Blocking Session Details

Useful queries for BLOCKING SESSIONS Analysis in both RAC and Non-RAC Database

Find out the SID,serial# to kill the inactive session.Once you determine these two things,u can proceed when  you get approval from the Authorised session User.

Step-1.Find the all blocking session info in RAC.Use only 'v$' if its not RAC enabled.Also ignore the 'INST_ID' columns in Non-RAC environments.

*BLOCKING session*

select * from v$lock where block>1;

select l1.inst_id,l1.sid
        , ' IS BLOCKING '
        , l2.inst_id,l2.sid
        from gv$lock l1, gv$lock l2
        where l1.block =1 and l2.request > 0
        and l1.id1=l2.id1
        and l1.id2=l2.id2);


col PROGRAM for a20
col EVENT for a20
col USERNAME for  a8
col WAIT_CLASS for a10
set lines 200
select inst_id, SID, serial#, BLOCKING_SESSION, BLOCKING_INSTANCE, rpad(WAIT_CLASS,8)
WAIT_CLASS,SECONDS_IN_WAIT,PROGRAM, MODULE , EVENT ,username
from gv$session where BLOCKING_SESSION is not null order by BLOCKING_SESSION,PROGRAM;


column sess format A20
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;


col sid format 999999
col username format a20
col osuser format a15
select b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a, v$process b
where a.paddr= b.addr
and a.sid=’&sid’;

select username,program,event,seconds_in_wait,status from gv$session where sid=’ ‘;