DBA Blocking Sessions

select
vwc.instance inst_id,
gs.sid||' - '||gs.serial# sid_serial#,
lpad(' ',2*(vwc.level_-1))||vwc.level_ level_,
xxen_util.user_name(gs.module,gs.action,gs.client_identifier) user_name,
xxen_util.responsibility(gs.module,gs.action) responsibility,
xxen_util.module_type(gs.module,gs.action) module_type,
xxen_util.module_name(gs.module) module_name,
lpad(' ',2*(vwc.level_-1))||gs.status||nvl2(vwc.blocker_sid,null,' - blocking') status,
xxen_util.time(vwc.in_wait_secs) time,
vwc.in_wait_secs seconds,
xxen_util.client_time(gs.prev_exec_start) prev_exec_start,
vwc.wait_event_text wait_event,
(select do.owner||'.'||do.object_name||' ('||do.object_type||')' from dba_objects do where case when vwc.wait_event_text like 'enq: TM%' then vwc.p2 when vwc.wait_event_text like 'enq: TX%' then vwc.row_wait_obj# end=do.object_id) object,
gs.sql_id,
gsa.sql_fulltext sql_text,
case when vwc.wait_event_text like 'enq: TX%' then (
select
'select * from '||do.owner||'.'||do.object_name||' where rowid='''||dbms_rowid.rowid_create(1,do.data_object_id,ddf.relative_fno,vwc.row_wait_block#,vwc.row_wait_row#)||'''' show_blocked_row
from
dba_objects do,
dba_data_files ddf
where
vwc.row_wait_obj#=do.object_id and
vwc.row_wait_file#=ddf.file_id
) end show_blocked_row,
lpad(' ',2*(level-1))||gs.module module,
gs.machine,
gs.username db_user,
gs.osuser,
gs.client_identifier client_id,
gs.action,
gs.program,
case when gs.type<>'BACKGROUND' then 'alter system disconnect session '''||gs.sid||','||gs.serial#||',@'||gs.inst_id||''' immediate;' end disconnect_db_session,
case when gs.type<>'BACKGROUND' then 'kill -9 '||gp.spid end kill_server_process,
vwc.root_blocking_sid
from
(
select
vwc.*,
level level_,
connect_by_root vwc.sid root_blocking_sid
from
v$wait_chains vwc
where
vwc.blocker_sid is not null or vwc.num_waiters>0
connect by nocycle
prior vwc.sid=vwc.blocker_sid and
prior vwc.sess_serial#=vwc.blocker_sess_serial# and
prior vwc.instance=vwc.blocker_instance
start with
vwc.blocker_sid is null
) vwc,
gv$session gs,
gv$process gp,
(
select distinct
gsa.sql_id,
min(gsa.inst_id) keep (dense_rank first order by gsa.inst_id, gsa.plan_hash_value) over (partition by gsa.sql_id) inst_id,
min(gsa.plan_hash_value) keep (dense_rank first order by gsa.inst_id, gsa.plan_hash_value) over (partition by gsa.sql_id) plan_hash_value
from
gv$sqlarea gsa
) gsa0,
gv$sqlarea gsa
where
(vwc.blocker_sid is not null or vwc.num_waiters>0) and
vwc.sid=gs.sid(+) and
vwc.sess_serial#=gs.serial#(+) and
vwc.instance=gs.inst_id(+) and
gs.inst_id=gp.inst_id(+) and
gs.paddr=gp.addr(+) and
gs.sql_id=gsa0.sql_id(+) and
gsa0.sql_id=gsa.sql_id(+) and
gsa0.inst_id=gsa.inst_id(+) and
gsa0.plan_hash_value=gsa.plan_hash_value(+)
connect by nocycle
prior vwc.sid=vwc.blocker_sid and
prior vwc.sess_serial#=vwc.blocker_sess_serial# and
prior vwc.instance=vwc.blocker_instance
start with
vwc.blocker_sid is null

select vwc.instance inst_id, gs.sid||' - '||gs.serial# sid_serial#, lpad(' ',2*(vwc.level_-1))||vwc.level_ level_, xxen_util.user_name(gs.module,gs.action,gs.client_identifier) user_name, xxen_util.responsibility(gs.module,gs.action) responsibility, xxen_util.module_type(gs.module,gs.action) module_type, xxen_util.module_name(gs.module) module_name, lpad(' ',2*(vwc.level_-1))||gs.status||nvl2(vwc.blocker_sid,null,' - blocking') status, xxen_util.time(vwc.in_wait_secs) time, vwc.in_wait_secs seconds, xxen_util.client_time(gs.prev_exec_start) prev_exec_start, vwc.wait_event_text wait_event, (select do.owner||'.'||do.object_name||' ('||do.object_type||')' from dba_objects do where case when vwc.wait_event_text like 'enq: TM%' then vwc.p2 when vwc.wait_event_text like 'enq: TX%' then vwc.row_wait_obj# end=do.object_id) object, gs.sql_id, gsa.sql_fulltext sql_text, case when vwc.wait_event_text like 'enq: TX%' then ( select 'select * from '||do.owner||'.'||do.object_name||' where rowid='''||dbms_rowid.rowid_create(1,do.data_object_id,ddf.relative_fno,vwc.row_wait_block#,vwc.row_wait_row#)||'''' show_blocked_row from dba_objects do, dba_data_files ddf where vwc.row_wait_obj#=do.object_id and vwc.row_wait_file#=ddf.file_id ) end show_blocked_row, lpad(' ',2*(level-1))||gs.module module, gs.machine, gs.username db_user, gs.osuser, gs.client_identifier client_id, gs.action, gs.program, case when gs.type<>'BACKGROUND' then 'alter system disconnect session '''||gs.sid||','||gs.serial#||',@'||gs.inst_id||''' immediate;' end disconnect_db_session, case when gs.type<>'BACKGROUND' then 'kill -9 '||gp.spid end kill_server_process, vwc.root_blocking_sid from ( select vwc.*, level level_, connect_by_root vwc.sid root_blocking_sid from v$wait_chains vwc where vwc.blocker_sid is not null or vwc.num_waiters>0 connect by nocycle prior vwc.sid=vwc.blocker_sid and prior vwc.sess_serial#=vwc.blocker_sess_serial# and prior vwc.instance=vwc.blocker_instance start with vwc.blocker_sid is null ) vwc, gv$session gs, gv$process gp, ( select distinct gsa.sql_id, min(gsa.inst_id) keep (dense_rank first order by gsa.inst_id, gsa.plan_hash_value) over (partition by gsa.sql_id) inst_id, min(gsa.plan_hash_value) keep (dense_rank first order by gsa.inst_id, gsa.plan_hash_value) over (partition by gsa.sql_id) plan_hash_value from gv$sqlarea gsa ) gsa0, gv$sqlarea gsa where (vwc.blocker_sid is not null or vwc.num_waiters>0) and vwc.sid=gs.sid(+) and vwc.sess_serial#=gs.serial#(+) and vwc.instance=gs.inst_id(+) and gs.inst_id=gp.inst_id(+) and gs.paddr=gp.addr(+) and gs.sql_id=gsa0.sql_id(+) and gsa0.sql_id=gsa.sql_id(+) and gsa0.inst_id=gsa.inst_id(+) and gsa0.plan_hash_value=gsa.plan_hash_value(+) connect by nocycle prior vwc.sid=vwc.blocker_sid and prior vwc.sess_serial#=vwc.blocker_sess_serial# and prior vwc.instance=vwc.blocker_instance start with vwc.blocker_sid is null