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 the script will generate it will execute Alter session to kill This Session .
Some Useful Link :
1-Erik Wramner
Thank you
Osama mustafa