Page Banner

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;