Identifying Blocking Sessions in Oracle Database

Below SQL gives the blocker and blockee sessions in database.

Oracle RAC Environment:

select (select username from gv$session where sid=a.sid) blocker, a.sid, 'is blocking', (select username from gv$session where sid=b.sid) blockee, b.sid from gv$lock a, gv$lock b where a.block > 0 and b.request > 0 and a.id1=b.id1 and a.id2=b.id2; Oracle Non-RAC Environment:

select (select username from v$session where sid=a.sid) blocker, a.sid, 'is blocking', (select username from v$session where sid=b.sid) blockee, b.sid from v$lock a, v$lock b where a.block > 0 and b.request > 0 and a.id1=b.id1 and a.id2=b.id2;

Leave a Reply

Your email address will not be published. Required fields are marked *