War against the Deadlock

==================ORACLE11G – 11.2.0.3.0=========================

Check Deadlock in RAC

=======
RAC
=======
prompt CTIME is in Seconds
set lines 120
col BLOCK for 9
col LMODE for 9
col INST_ID for 9
col REQUEST for 9
col SID for 999999
select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK
from gv$lock where (ID1,ID2,TYPE) in
(select ID1,ID2,TYPE from gv$lock where request>0);

Kill the locked sessions in RAC

Workaround-I

SELECT 'ALTER SYSTEM KILL SESSION  ' ||chr(39) ||s.sid||chr(44)||s.serial#||',@'||INST_ID||chr(39)||' immediate;'
from (SELECT INST_ID,SID,SERIAL# FROM GV$SESSION
WHERE SID IN (select SID from gv$lock where (ID1,ID2,TYPE) in (select ID1,ID2,TYPE from gv$lock where request>0))) s;

Workaround-II

SET PAGESIZE 5000
SET SERVEROUTPUT ON
BEGIN
FOR S IN (SELECT INST_ID,SID,SERIAL# FROM GV$SESSION WHERE SID IN (SELECT SID FROM GV$LOCK WHERE (ID1,ID2,TYPE) IN (SELECT ID1,ID2,TYPE FROM GV$LOCK WHERE REQUEST>0)) )
LOOP
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION  ' ||CHR(39) ||S.SID||CHR(44)||S.SERIAL#||',@'||S.INST_ID||CHR(39)||' IMMEDIATE' ;

DBMS_OUTPUT.PUT_LINE('ALTER SYSTEM KILL SESSION  ' ||CHR(39) ||S.SID||CHR(44)||S.SERIAL#||',@'||S.INST_ID||CHR(39)||' IMMEDIATE' );

--EXECUTE IMMEDIATE 'TRUNCATE TABLE NETVERTEX.TBLMNOTIFICATIONQUEUE';

END LOOP;
END;
/

🙂

Download

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.