Kill inactive session in oracle database

We had a requirement where the DB connections which are in INACTIVE state for atleast 4 hours shall be cleared from the session to free resources. here we will discuss how to automatically clean/ kill inactive session in oracle database using DBMS job.

Overall impact of an idle session on a server is usually negligible. However, that session could own shared and exclusive locks and cause lock contention for other sessions required shared or exclusive locks on the same resources/data.

First we need to update the profile to set IDLE_TIME to 4(240 mins) hours. You can change the time value according to your requirement and policy.

Step 1:

Login to the database as SYSDBA User and execute the below command

ALTER PROFILE default LIMIT IDLE_TIME  240;

Step 2:

Login to the database as SYSDBA and create the stored procedure specified below. You can execute this as a script

CREATE OR REPLACE PROCEDURE proc_kill_inactive_sessions IS
   CURSOR kill_sessions_cur
   IS
      SELECT s.SID, s.serial#
        FROM v$session s
       WHERE s.status = 'SNIPED';

   v_cmd   VARCHAR2 (100);
BEGIN
   FOR kill_sessions_rec IN kill_sessions_cur
   LOOP
      v_cmd :=
            'Alter system kill session '''
         || kill_sessions_rec.SID
         || ','
         || kill_sessions_rec.serial#
         || ''' immediate';

      EXECUTE IMMEDIATE v_cmd;
   END LOOP;
END;
/

Step 3:

Login to the database as SYSDBA and execute the below anonymous block as a script for creating the DBMS job.

Note: Replace the value 19/11/2014 16:00:01 with NEXT 1 hour value.

Ex: If you are running the below block at 20/11/2014 14:00:01 then specify as 20/11/2014 15:00:01

Script:

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

commit;

Step 4:

Verify Job & inactive sessions
The job can be verified whether it has created successfully or not by running the below query

select * from dba_jobs;

Post the job is appearing in above query, wait for 4 hours and observe whether the inactive sessions are cleared out or not by running the below query

SELECT DISTINCT *
  FROM gv$session 
 WHERE     last_call_et > 14400
       AND username IS NOT NULL
       AND username NOT IN  ('SYS',
                    'SYSTEM',
                    'DBSNMP',
                    'TSMSYS',
                    'OUTLN',
                    'EXFSYS',
                    'ORDSYS',
                    'WMSYS',
                    'XDB',
                    'MDSYS'
                   )
       AND status = 'INACTIVE';

Once all above is set in your database, the DBMS job will automatically clear/ kill inactive session in oracle database which is INACTIVE for more than 4 hours.

If you want to terminate a ACTIVE or INACTIVE session manually then you can refer Terminating Sessions from oracle for the same.

 

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.