set serveroutput on
set feedback off
set linesize 300
declare
blockcount number;
sqlreport varchar(3000);
sqloutput varchar(5000);
v_rec varchar(3000);
cursor blk_curs is select B.USERNAME BUSER,B.SID BSID,B.SERIAL# BSERIAL,B.INST_ID BID,B.STATUS BSTAT,B.LAST_CALL_ET BLAST,W.USERNAME WUSER,W.SID WSID,W.SERIAL# WSERIAL,W.INST_ID WID
from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET from gv$session where BLOCKING_SESSION >0) W,
(select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B
where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');
cursor distval is select distinct B.SID CSID, B.USERNAME CUSER from (select INST_ID,SID,SERIAL#,USERNAME,STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION, LAST_CALL_ET
from gv$session where BLOCKING_SESSION >0) W, (select INST_ID,SID,SERIAL#,USERNAME,STATUS,LAST_CALL_ET from gv$session ) B
where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');
cursor killcurs(v in varchar, b in number) is select SID ,serial# SERIAL,inst_id ,username ,to_char(logon_time,'''DD-MON-YYYY HH24-MI-SS''') OTIME,event,status ,machine ,round(LAST_CALL_ET/60,2) OMIN
FROM gv$session where username is not null and username=v and sid=b order by inst_id;
begin
DBMS_OUTPUT.put_line('Checking for blocking sessions on this database');
DBMS_OUTPUT.put_line('-----------------------------------------------');
select count(*) into blockcount from (select * from gv$session where BLOCKING_SESSION >0) W,
(select * from gv$session ) B where W.BLOCKING_INSTANCE=B.INST_ID and W.BLOCKING_SESSION=B.SID and B.username not in ('SYS','SYSTEM','DBSNMP','RMAN');
IF blockcount > 0 THEN
DBMS_OUTPUT.put_line('Found blocking sessions -> Fetching report for the same');
DBMS_OUTPUT.put_line('-------------------------------------------------------');
for v_rec in blk_curs LOOP
dbms_output.put_line(v_rec.BUSER ||' ('||v_rec.BSID||','||v_rec.BSERIAL||',@'||v_rec.BID||') is Currently '||v_rec.BSTAT||' for last '||v_rec.BLAST||' Sec and it''s BLOCKING user '|| v_rec.WUSER||' ('||v_rec.WSID||','||v_rec.WSERIAL||',@'||v_rec.WID||')');
end loop;
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('Further details on blocking sessions -> includes kill script of blocking session');
DBMS_OUTPUT.put_line('--------------------------------------------------------------------------------');
for v_rec2 in distval LOOP
for v_rec3 in killcurs(v_rec2.CUSER, v_rec2.CSID) loop
dbms_output.put_line('alter system kill session '''||v_rec3.SID|| ',' || v_rec3.SERIAL||',@'||v_rec3.inst_id|| ''' immediate; '||v_rec3.username||' '||v_rec3.OTIME||' '|| v_rec3.event||' '||v_rec3.status||' '||v_rec3.machine||' '||v_rec3.OMIN);
end loop;
end loop;
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
ELSE
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('Hurrey !!! No blocking sessions found');
DBMS_OUTPUT.put_line('-');
DBMS_OUTPUT.put_line('-');
END IF;
END;
/