Kill blocking sessions

-- ------------
-- Kill blocking sessions RAC aware
-- ------------

set serveroutput on

declare
blockers   VARCHAR2 (1000);
BEGIN
FOR x IN (SELECT lka.SID sessid, ses.serial# serial,
      lka.inst_id instance_id
      FROM gv$lock lka, gv$lock lkb, gv$session ses
      WHERE (lka.id1, lka.id2) IN
      (SELECT id1, id2
      FROM gv$lock
      WHERE request = 0
      INTERSECT
      SELECT id1, id2
      FROM gv$lock
      WHERE lmode = 0)
      AND lka.id1 = lkb.id1
      AND lka.id2 = lkb.id2
      AND lka.request = 0
      AND lkb.lmode = 0
      AND lka.SID = ses.SID
      AND lka.inst_id = ses.inst_id)
LOOP
blockers :=
'ALTER SYSTEM KILL SESSION '''
|| x.sessid
|| ','
|| x.serial
|| ',@'
|| x.instance_id
|| '''';
DBMS_OUTPUT.new_line;
DBMS_OUTPUT.put_line ('Looking for blocking sessions:');
DBMS_OUTPUT.put_line (blockers);
END LOOP;
END;
/