检测数据库各实例session 阻塞 tree的sql

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  
;