PL/SQL script to find Blocking Sessions/Objects and to Kill them

By | July 9, 2013
/*********************************************************
*PURPOSE: To findout blocking sessions and kill them     *
*AUTHOR: Shailender Thallam                              *
**********************************************************/
--
DECLARE
  CURSOR c
  IS
     SELECT c.owner,
      c.object_name,
      c.object_type,
      b.SID        ,
      b.serial#    ,
      b.STATUS     ,
      b.osuser     ,
      b.machine
       FROM v$locked_object a,
      v$session b            ,
      dba_objects c
      WHERE b.SID   = a.session_id
    AND a.object_id = c.object_id
    --AND b.osuser    = 'shailender.thallam'
    --and c.object_name in ('JSW_CRM_C_HR_COIL_INFO','T_SPCL_BARCODE_CRS2')
      ;
  --
  CURSOR d
  IS
     SELECT SID,
      serial#
       FROM v$session s
      WHERE s.blocking_session IS NOT NULL
    --AND osuser                  = 'shailender.thallam';
  --
  c_row c%ROWTYPE;
  d_row d%ROWTYPE;
  --
  l_sql VARCHAR2(100);
BEGIN
  OPEN c;
  LOOP
    FETCH c INTO c_row;
    EXIT
  WHEN c%NOTFOUND;
    dbms_output.put_line('in c  '||c_row.SID);
    l_sql := 'alter system kill session '''||c_row.SID||','||c_row.serial#||'''';
    EXECUTE IMMEDIATE l_sql;
    COMMIT;
  END LOOP;
  CLOSE c;
  --
  --
  --
  OPEN d;
  LOOP
    FETCH d INTO d_row;
    EXIT
  WHEN d%NOTFOUND;
    dbms_output.put_line('in d  '||d_row.SID);
    l_sql := 'alter system kill session '''||d_row.SID||','||d_row.serial#||'''';
    EXECUTE IMMEDIATE l_sql;
    COMMIT;
  END LOOP;
  CLOSE d;
END;
/
--
WOW! Did you like this post? We'll send more interesting posts like PL/SQL script to find Blocking Sessions/Objects and to Kill them to you!
Enter your Email Address: