Wednesday, November 26, 2014

Script/ Auto job to kill inactive sessions for more than 30 minutes


How to kill inactive sessions?

1. Create the procedure to select the sessions whose last call exceed 30 minutes and current status is in active.

CREATE OR REPLACE PROCEDURE PROC_KILL_INACTIVE_SESSION is

STMT VARCHAR2(1000);

BEGIN

 FOR X IN (
           SELECT SID, SERIAL# FROM V$SESSION
            WHERE STATUS = 'INACTIVE'
            AND (last_call_et / 60) > 30
          )
 LOOP

-- generate the script for killing in active sessions

   STMT := 'ALTER SYSTEM KILL SESSION ''' ||X.SID ||',' ||X.SERIAL# ||'''' ;
              DBMS_OUTPUT.PUT_LINE( STMT );
   EXECUTE IMMEDIATE STMT;
 END LOOP;

END;

2. Create an auto job to run after 30 minutes for killing inactive sessions

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    ( job       => X
     ,what      => 'PROC_KILL_INACTIVE_SESSION;'
     ,next_date => to_date('01/01/4000 00:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval   => 'SYSDATE+30/1440'
     ,no_parse  => TRUE
    );
  SYS.DBMS_JOB.BROKEN
   (job    => X,
    broken => TRUE);
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

commit;