/*
 * Title  : ash_blk_ses.sql
 * Author : EXADIME LLC
 * Created: 11-Dec-2022
 * Updated: 05-Jun-2023
 * Release: 1.0.0
 * Purpose: Shows blocked sessions including SQL text captured in ASH.
 * Comment: Doc ID 2406026.1
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on
set termout on

set linesize 250
set pagesize 0
set pause off
set long 80

set heading on
set headsep on
set underline on
set colsep " "

set define on
set verify off
set escape off
set embedded on

set wrap off

column rownum			format 999999		heading "#"
column time		        format a18		heading "Time"
column inst_id			format 999999		heading "Instance"
column blocked_session_id	format 999999		heading "Session"
column blocked_session_serial#	format 999999		heading "Serial#"
column username			format a20		heading "Username"
column current_object		format a30		heading "Object"
column seconds_in_wait          format 999999		heading "Seconds|Wait"
column sql_id                   format a13		heading "SQL Id"
column sql_text                 format a30		heading "Blocked|SQL Text"
column blocking_inst_id 	format 999999		heading "Instance"
column blocking_session 	format 999999		heading "Session"
column blocking_session_status  format 999999		heading "Blocking|Status"

accept p_time  char prompt "Enter wait time in seconds [default 3]: " default 3
accept p_sample_time_begin char prompt "Enter begin sample time in format [yyyymmdd hh24:mi:ss]: "
accept p_sample_time_end char prompt "Enter end sample time in format [yyyymmdd hh24:mi:ss]: "
accept p_order char prompt "Enter order by column {1|10} [asc|desc]: " default 10

spool &&rpt_dir/ash_blk_ses.rpt

select
	row_number() over(order by &&p_order)	sno,
	time,
	inst_id,
	blocked_session_id,
	blocked_session_serial#,
	username,
	current_object,
	seconds_in_wait,
	sql_id blocked_sql_id,
	sql_text blocked_sql_text,
	blocking_inst_id,	
	blocking_session,
	blocking_session_status
from(
	select 
		to_char(s.sample_time,'yyyymmdd hh24:mi:ss') time,
		s.inst_id,
		s.session_id blocked_session_id,
		s.session_serial# blocked_session_serial#,
		(select substr(username,1,20) from dba_users where user_id=s.user_id) username,
		(select object_name from dba_objects where object_id=s.current_obj#) current_object,
		s.time_waited/1000000 seconds_in_wait,
		s.sql_id,
		to_char(substr(q.sql_fulltext,1,30)) sql_text,
		s.blocking_inst_id,	
		s.blocking_session,
		s.blocking_session_status
	from 
		gv$sql q,
		gv$active_session_history s
	where 
		s.sql_id=q.sql_id
	and	s.inst_id=q.inst_id
	and	s.blocking_session is not null
	and	s.user_id<>0
	and 	s.time_waited/1000000>&&p_time
	and     s.sample_time between to_date('&&p_sample_time_begin','yyyymmdd hh24:mi:ss') 
	and 	to_date('&&p_sample_time_end','yyyymmdd hh24:mi:ss')
)
order by 
	&&p_order;

spool off

clear columns

set embedded off
set escape off
set verify on
set define on

set colsep " "
set underline on
set headsep on
set heading on

set pause off
set pagesize 24
set linesize 80
set long 80

set termout on
set serverout off
set feedback on
set echo off
