Script - List blocking session tree in Oracle single instance or RAC database

 File Name

  sess_blocking_tree.sql

Description

  Displays Oracle databsae session blocking tree.

  It support both single instance and RAC

Requirements

 User needs SELECT privilege on following views

    gv$instance

    gv$session

Usage

  Run with SQL*Plus or SQLcl

  @sess_blocking_tree

-- -----------------------------------------------------------------------------------
-- File Name    : sess_blocking_tree.sql
-- Author       : https://www.dbaplus.ca
-- Description  : Displays session blocking heirarchy
-- Requirements : Access to the following views
--                gv$instance
--                gv$session
-- Usage        : @sess_blocking_tree
-- Last Modified: 25-Nov-2024
-- -----------------------------------------------------------------------------------
set termout off
-- ----------------------------------------------------
-- Save current SET variable
-- ----------------------------------------------------
column v_set_save_file new_value V_SET_SAVE_FILE
select 'tmp_SQLPlus_ENV_&_USER' v_set_save_file from dual;
STORE SET &V_SET_SAVE_FILE replace
-- ----------------------------------------------------
-- Save current NLS_DATE_FORMAT
-- ----------------------------------------------------
column v_nls_date_format new_value V_NLS_DATE_FORMAT
select sys_context('USERENV','NLS_DATE_FORMAT') v_nls_date_format from dual;
-- ------------------------------------------------------------------------
-- Show INST_ID column when RAC database has more than one instance running
-- ------------------------------------------------------------------------
column v_inst_id new_value V_INST_ID
select decode(count(*),1,'','INST_ID,') v_inst_id  from gv$instance;
-- ------------------------------------------------------------------------
-- SET env variable
-- ------------------------------------------------------------------------
set linesize 512
set pagesize 999
set verify off
column username format a30
column osuser format a10
column machine format a25
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
set termout on
----------------------------------------------------------------------------- 
SELECT level,
       LPAD(' ', (level-1)*2, ' ') || NVL(s.username, '(oracle)') AS username,
       s.osuser, &V_INST_ID
       s.sid,
       s.serial#,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       s.logon_Time
from   gv$session s
where  level > 1
       or exists (select 1
               from   gv$session
               where  blocking_session = s.sid and blocking_instance=s.inst_id)
connect by prior s.sid = s.blocking_session
           and prior s.inst_id=s.blocking_instance
start with s.blocking_session is null;
---------------------------------------------
-- Restore SET variable and NLS_DATE_FORMAT
---------------------------------------------
set termout off
start &V_SET_SAVE_FILE
alter session set nls_date_format='&V_NLS_DATE_FORMAT';
column v_set_save_file clear
column v_nls_date_format clear
column v_inst_id clear
set termout on