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;
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:
Posta un commento