Answer: You can query the
gv$lock and gv$session views to locate a blocking session in RAC.
Killing a session in RAC is different than killing an ordinary blocking
session, and dba_blockers and dba_waiters cannot
always help identify blocking RAC sessions.
Miladin Modrakovic offers this script to detect and
kill RAC blocking sessions, using
gv$session and gv$lock:
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 kill_blovk;
END TEST;
/
When you run this script it will generate the alter system
kill session syntax for the RAC blocking session:
SQL> set serveroutput on
SQL> exec kill_blocker;
ALTER SYSTEM KILL SESSION ‘115,9779,@1′
PL/SQL procedure successfully completed.
Also see these related notes on Oracle blocking
sessions:
find blocking sessions with
v$session
find the data block for a
blocking
session
