Oracle : How to Kill Blocking Sessions in RAC
Dear Readers, In this article, we will see Kill blocking sessions in RAC . Kill blocking sessions in RAC:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
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; / |
Output:
|
1 2 3 4 5 6 7 8 9 10 |
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 … Read More
