Oracle : How to Kill Blocking Sessions in RAC

Share via:

Post Views: 0

Dear Readers,

In this article, we will see Kill blocking sessions in RAC .

Kill blocking sessions in RAC:

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;
/

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('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;

DBMS_OUTPUT.put_line('-');

ELSE

DBMS_OUTPUT.put_line('-');

DBMS_OUTPUT.put_line('Hurrey !!! No blocking sessions found');

DBMS_OUTPUT.put_line('-');

END IF;

END;

Output:

Checking for blocking sessions on this database
------------------------------------------------
Found blocking sessions -> Fetching report for the same
--------------------------------------------------------
VINOD (3033,27215,@1) is Currently INACTIVE for last 23 Sec and it's BLOCKING user VINOD (2126,11412,@2)
-
-
Further details on blocking sessions -> includes kill script of blocking session
--------------------------------------------------------------------------------
alter system kill session '3033,27215,@1' immediate, VINOD '02-NOV-2020 19-12-04' SQL*Net message from client INACTIVE server1 .38

Checking for blocking sessions on this database

------------------------------------------------

Found blocking sessions -> Fetching report for the same

--------------------------------------------------------

VINOD (3033,27215,@1) is Currently INACTIVE for last 23 Sec and it's BLOCKING user VINOD (2126,11412,@2)

Further details on blocking sessions -> includes kill script of blocking session

--------------------------------------------------------------------------------

alter system kill session '3033,27215,@1' immediate, VINOD '02-NOV-2020 19-12-04' SQL*Net message from client INACTIVE server1 .38

Thank you for giving your valuable time to read the above information.
Follow us on
Website : www.ktexperts.com
Facebook Page : KTexperts
Linkedin Page : KT EXPERTS