Wednesday, June 12, 2013

how to check blockers in oracle 10g, 11g


set echo off
column blocker format a11;
column blockee format a10;
column sid format 99999;
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
/


select ctime/60,sid from v$lock where block>0;  
select program,ACTION,STATUS,serial# from v$session where sid='&sid';

SELECT substr(DECODE(request,0,'Holder: ','Waiter: ')||sid,1,12) sess, id1, id2, lmode, request, type FROM GV$LOCK WHERE (id1, id2, type) IN (SELECT id1,id2,type FROM GV$LOCK WHERE request>0) order by id1;

select sid, serial#, action, module, program, status, last_Call_ET/60 as LCET_min from gv$session where sid in
(select session_id from dba_locks where blocking_others='Blocking')

select sid, b.serial#, osuser, b.username, status, process, paddr, spid
from v$process a,v$session b
where sid = '&sid'
and a.addr = b.paddr
and b.username != ' '
order by 1

select sql_text
  from   v$session, v$sqltext
  where v$session.sql_address = v$sqltext.address
  and   V$session.sql_hash_value = v$sqltext.hash_value
  and  sid = &sid
order by piece


No comments:

Post a Comment