Tuesday, October 30, 2012

Find blocking sessions In Oracle

  You can query the gv$lock and gv$session views to locate a blocking session in RAC.

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
 


 

No comments:

Post a Comment