Who is blocking sessions in Oracle

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
/

Post a Comment

And that's all there is to it!

If anyone has any other questions or requests for future How To posts, you can either ask them in the comments or email me. Please don't feel shy at all!

I'm certainly not an expert, but I'll try my hardest to explain what I do know and research what I don't know.

Previous Post Next Post