Who is blocking sessions in Oracle
set echo off
column blocker format a10;
column blockee format a10;
alter session set optimizer_mode=rule;
select
(select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ' "IS BLOCKING",
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
/
alter session set optimizer_mode=choose;
set lines 200
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 l2.ID2 = l2.ID2
/
column blocker format a10;
column blockee format a10;
alter session set optimizer_mode=rule;
select
(select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ' "IS BLOCKING",
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
/
alter session set optimizer_mode=choose;
set lines 200
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 l2.ID2 = l2.ID2
/
Tags:
Oracle