blocking_chain

col object_name for a20
col username for a14
col OBECT_TYPE for a20
Col owner for a20

with locks as(
select session_id,lock_type, mode_held,mode_requested,lock_id1,lock_id2,blocking_others
from sys.dba_lock), waiters as (
select w.session_id waiting_session, h.session_id holding_session, w.lock_type lock_type, h.mode_held mode_held, w.mode_requested mode_requested, w.lock_id1, w.lock_id2
from locks w, locks h
where h.blocking_others = 'Blocking' and h.mode_held != 'None' and h.mode_held != 'Null' and w.mode_requested != 'None' and w.lock_type = h.lock_type and w.lock_id1 = h.lock_id1 and w.lock_id2 = h.lock_id2), blockers as(
select holding_session waiting_session, to_number(null) holding_session, 'None' lock_type, null mode_held, null mode_requested, null lock_id1, null lock_id2
from waiters minus
select waiting_session waiting_session, to_number(null) holding_session, 'None' lock_type, null mode_held, null mode_requested, null lock_id1, null lock_id2
from waiters), waiters_blockers as(
select waiting_session,holding_session,lock_type,mode_held,mode_requested,lock_id1,lock_id2
from waiters union all
select waiting_session,holding_session,lock_type,mode_held,mode_requested,lock_id1,lock_id2
from blockers), lock_objects as(
select session_id,cnt lock_obj_count, case when cnt = 1 then object_name else null end object_name, object_id,owner,object_type from(
select session_id,count(distinct object_name) cnt,max(object_name) object_name, max(lo.object_id) object_id, max(ao.owner) owner,max(object_type) object_type
from gv$locked_object lo,all_objects ao,waiters_blockers wb
where lo.object_id = ao.object_id and (wb.waiting_session = lo.session_id or wb.holding_session = lo.session_id) group by session_id)), wait_block_ses as(
select waiting_session,holding_session,lock_type,mode_held,mode_requested, s.inst_id, s.SECONDS_IN_wait, s.username,s.osuser,s.machine,s.CLIENT_INFO,s.CLIENT_IDENTIFIER,o.object_name,o.owner,o.object_id,module,o.object_type,o.lock_obj_count,
(select command_name
from v$sqlcommand
where command_type = s.command ) "Command"
from waiters_blockers wb, gv$session s, lock_objects o
where s.sid = wb.waiting_session and o.session_id(+) = wb.waiting_session )
select case when holding_session is null then 'Lock' else 'Lock Waiting' end status, waiting_session, username,osuser,machine, round(seconds_in_wait/60, 2) as wtmin, object_name, object_id,owner,object_type
from wait_block_ses start with holding_session is null connect by prior waiting_session = holding_session;