Oraclue

Oracle internals, debugging and undocumented features

Procedure to kill blocking session in RAC 11g

Many times I tried to kill session in RAC  and then get message back saying that session cannot be killed because my session is connected to different instance.Then I would login to instance where blocking sessions runs  and kill it.

Starting with 11g , Oracle included instance name in alter system kill command (3thd parameter or  @instance_id ) .So to kill blocking session on any node from any node I would run:

SQL> set serveroutput on
SQL> exec kill_blocker;

ALTER SYSTEM KILL SESSION ‘115,9779,@1’

PL/SQL procedure successfully completed.

Code is below.It is standard code for detecting blocking session in RAC which can be found on google.I just modified it to include instance id and placed into procedure

CREATE OR REPLACE PROCEDURE kill_blocker
AS
sqlstmt   VARCHAR2 (1000);
BEGIN
FOR x IN (SELECT gvh.SID sessid, gvs.serial# serial,
gvh.inst_id instance_id
FROM gv$lock gvh, gv$lock gvw, gv$session gvs
WHERE (gvh.id1, gvh.id2) IN (SELECT id1, id2
FROM gv$lock
WHERE request = 0
INTERSECT
SELECT id1, id2
FROM gv$lock
WHERE lmode = 0)
AND gvh.id1 = gvw.id1
AND gvh.id2 = gvw.id2
AND gvh.request = 0
AND gvw.lmode = 0
AND gvh.SID = gvs.SID
AND gvh.inst_id = gvs.inst_id)
LOOP
sqlstmt :=
‘ALTER SYSTEM KILL SESSION ”’
|| x.sessid
|| ‘,’
|| x.serial
|| ‘,@’
|| x.instance_id
|| ””;
DBMS_OUTPUT.put_line (sqlstmt);

EXECUTE IMMEDIATE sqlstmt;
END LOOP;
END kill_blocker;
/

Just a note. If you are not comfortable killing session owned by SYS than exclude them from query…

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: