/*
	Syntax: @blockers <where clause> <w.COL1,COL2,...,b.COL1,b.COL2,...,f.COL1,f.COL2...>
				w.COLx for waiter cols
				b.COLx for blocker cols
				f.COLx for final blocker cols
				
	vplesnila 2025-08-29 - creation
*/


col event for a25 trunc
col username for a20 
col machine for a30 
col osuser for a20 
col action for a30 
col module for a30 
col program2 for a15 trunc heading "Program2"
col program for a25 trunc heading "Program"
col inst_id for 9 head "I#"
col blocking_instance for 9 head "B|I#"
col blocking_session for 9999999 head "B|SID"
col blocking_session_status for a10 head "Blocking|status"
col final_blocking_instance for 9 head "FB|I#"
col final_blocking_session for 9999999 head "FB|SID"
col final_blocking_session_status for a10 head "Final|Blocking|status"
col blocking_serial head "B|serial#"
col final_blocking_serial head "FB|serial#"

col blocked_sess_str for a15 heading "Blocked" justify left
col blocking_sess_str for a15 heading "Blocking" justify left
col final_blocking_sess_str for a15 heading "Final|Blocking" justify left

select 
	&&2
	,w.sid||','||w.serial#||'@'||w.inst_id blocked_sess_str
	,w.blocking_session||','||w.blocking_serial||'@'||w.blocking_instance blocking_sess_str
	,case
		when w.blocking_session=w.final_blocking_session and  w.blocking_instance=w.final_blocking_instance then null
		else w.final_blocking_session||','||w.final_blocking_serial||'@'||w.final_blocking_instance
     end final_blocking_sess_str
from (
		select 
			s1.*
			,s2.serial# blocking_serial
			,s3.serial# final_blocking_serial
			,CASE WHEN s1.type = 'BACKGROUND' OR REGEXP_LIKE(s1.program, '.*\([PJ]\d+\)') THEN
					REGEXP_REPLACE(SUBSTR(s1.program,INSTR(s1.program,'(')), '\d', 'n')
				ELSE
					'('||REGEXP_REPLACE(REGEXP_REPLACE(s1.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
			END || ' ' program2
		from 
			gv$session s1 
			inner join gv$session s2 on s1.blocking_session=s2.sid and s1.blocking_instance=s2.inst_id
			inner join gv$session s3 on s1.final_blocking_session=s3.sid and s1.final_blocking_instance=s3.inst_id
		where 
			s1.blocking_session is not null
) w
inner join gv$session b on b.sid=w.blocking_session and b.inst_id=w.inst_id
inner join gv$session f on f.sid=w.final_blocking_session and f.inst_id=w.inst_id
where 
	&&1
order by
	final_blocking_sess_str desc,
	blocking_sess_str desc
/