DBA的日常功能SQL之一，绝对原创。
由于使用到了 v$sesion 的 blocking_instance,blocking_session 这两列，所以数据库的最低版本为oracle 10g。
适合单实例和多实实例数据库，一个SQL搞定。
with/*+ materialized +*/ aa as (
select *
from gv$session s
where 1=1
)
select
'alter system kill session ''' || t.SID || ',' || t.SERIAL# || ''' immediate;' as kill_SID,
lpad('+', 2 * level - 1) ||t.inst_sid as leveL_inst_sid,
level level_flag,
sys_connect_by_path(t.inst_sid,'/') as inst_sid_path,
connect_by_root t.inst_sid as root_inst_sid,
connect_by_isleaf as node_isleaf,
'##########' as FLAGFLAG0,
t.status,
t.event#,
t.event,
t.program,
t.module,
t.action,
t.client_identifier,
t.OSUSER os_USER,
t.machine os_machine,
t.logon_time logon_time,
t.last_call_et last_call_et,
'alter system kill session ''' || t.SID || ',' || t.SERIAL# || ''' immediate;' as kill_SID,
'##########' as FLAGFLAG/*,
t.* */
from (
select gs.inst_id||'-'||gs.sid as inst_sid,
gs.blocking_instance||'-'||gs.blocking_session par_inst_sid,
gs.*
from aa gs
where 1=1
and (gs.inst_id,gs.sid ) in (
--被阻塞者
select s.inst_id,s.sid
from aa s
where 1=1
and s.BLOCKING_SESSION is not null
union all
--阻塞者
select sw.inst_id,sw.sid
from aa sw
where 1=1
--and lower(sw.MACHINE) not like '%ejb%'
and (sw.INST_ID,sw.SID) in (
select/*+ hash_sj*/
s.BLOCKING_INSTANCE,
s.BLOCKING_SESSION
from aa s
where 1=1
and s.BLOCKING_SESSION is not null
)
)
) t
where 1=1
connect by prior t.inst_sid =t.par_inst_sid
order siblings by t.inst_sid
;