Reports 2017-11-18T12:27:27+00:00

DBA Blocking Sessions

Description
Categories: DBA, Enginatics
Mutually blocking database locks showing blocking and waiting session information

select
gi.instance_name instance,
gl.sid,
gs.serial#,
gs.blocking_session blocking_sid,
gs.final_blocking_session final_blocking_sid,
gs.status||decode(gl.block,1,' - blocking') status,
gs.prev_exec_start,
gs.event,
xxen_util.responsibility(gs.action) responsibility,
xxen_util.module_type(gs.module) module_type,
xxen_util.module_name(gs.module) module_name,
xxen_util.user_name(gs.client_identifier) user_name,
gs.module,
do.object_type,
do.owner,
do.object_name,
gs.machine,
case when do.object_type='TABLE' then dbms_rowid.rowid_create(1, gs.row_wait_obj#, gs.row_wait_file#, gs.row_wait_block#, gs.row_wait_row#) end row_id,
gs.prev_sql_id,
gs.username db_user,
gs.osuser,
gs.action,
gs.program,
'alter system kill session '''||gs.sid||','||gs.serial#||',@'||gs.inst_id||''' immediate;' kill_string
from
(select distinct gl.inst_id, gl.sid, max(gl.block) over (partition by gl.inst_id, gl.sid) block from gv$lock gl where gl.request>0 or gl.block>0) gl,
gv$instance gi,
gv$session gs,
dba_objects do
where
1=1 and
gl.inst_id=gs.inst_id and
gl.sid=gs.sid and
gs.inst_id=gi.inst_id and
gs.row_wait_obj#=do.object_id(+)
order by
nvl(gs.blocking_session,gs.sid),
nvl2(gs.blocking_session,1,0),
gs.prev_exec_start

Parameter Name SQL text Validation
Object Name
do.object_name like upper(:object_name)
LOV