Translation

The oldest posts, are written in Italian. If you are interested and you want read the post in English, please use Google Translator. You can find it on the right side. If the translation is wrong, please email me: I'll try to translate for you.

mercoledì, giugno 22, 2011

Who is blocking me?

col username for a25
col IS_BLOCKED_BY for a20
col state for a40
col sql_id for a15
col obj for a80
set lines 220
set trim on

select
  nvl(USERNAME,schemaname)||' ('||SID||':'||INST_ID||')' USERNAME
 ,SQL_ID
 ,nvl2(BLOCKING_SESSION,BLOCKING_SESSION||':'||BLOCKING_INSTANCE||':'||
   (select
      sql_id
    from
      gv$session holder
    where
      holder.inst_id= s.BLOCKING_INSTANCE
    and holder.sid=s.BLOCKING_SESSION), null) IS_BLOCKED_BY
 ,decode(state, 'WAITING', 'WAITING', 'ON CPU')||'
 ('||substr(event,1,40)||')' state
 ,ROW_WAIT_FILE#||':'||ROW_WAIT_BLOCK#||':'||ROW_WAIT_ROW#||'
'||case
    when ROW_WAIT_OBJ# >0 then
     (select owner||':'||object_name||':'||nvl(subobject_name,'=nosub=')||' ('||object_type||')'
      from
       dba_objects do
      where
       do.object_id=s.ROW_WAIT_OBJ#)
  end obj
from
  gv$session s
where
  event in
    (select
       NAME
     from
       v$event_name
     where
       WAIT_CLASS != 'Idle')
order by sid;

Nessun commento: