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.
Login to the database as SYSDBA User and execute the below command
ALTER PROFILE default LIMIT IDLE_TIME 240;
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; /
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
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;
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.