如题
with lk as (select blocking_instance||'.'||blocking_session blocker, inst_id||'.'||sid waiter
from gv$session where blocking_instance is not null and blocking_session is not null)
select lpad(' ',2*(level-1))||waiter lock_tree from
(select * from lk union all
select distinct 'root', blocker from lk
where blocker not in (select waiter from lk))
connect by prior waiter=blocker start with blocker='root';
改写为v$lock
试试
select 'blocker(' || lb.sid || ':' || sb.username || ')-sql:' || qb.sql_text blockers,
'waiter (' || lw.sid || ':' || sw.username || ')-sql:' || qw.sql_text waiters
from v$lock lb, v$lock lw, v$session sb, v$session sw, v$sql qb, v$sql qw
where lb.sid = sb.sid
and lw.sid = sw.sid
and sb.prev_sql_addr = qb.address
and sw.sql_address = qw.address
and lb.id1 = lw.id1
and sw.lockwait is not null
and sb.lockwait is null
and lb.block = 1;
