HOW TO TERMINATE ALL IDLE SESSIONS/PROCESSES IN AN ORACLE DATABASE AUTOMATICALLY
REQUEST
DETAILS
The following statement shows idle time for all inactive sessions in a database
SET LINES 300
SET PAGES 999
COL OSUSER FOR A20
COL DATABASE_USER FOR A20
COL MACHINE FOR A20
COL MODULE FOR A10
COL PROGRAM FOR A40
COL INACTIVE_TIME FOR A15
------------------------------------------------------------
-- Victor Khalturin
-- Version 16:12 12.08.2020 (002)
--
-- Find out all inactive sessions/processes and their idle time.
------------------------------------------------------------
SELECT S.SID,
S.SERIAL#,
S.USERNAME DATABASE_USER,
TO_CHAR(LOGON_TIME, 'DD-MON-YYYY HH24:MI:SS' ) LOGON_TIME,
S.STATUS,
S.MACHINE,
S.PORT,
S.PROGRAM,
CASE
WHEN LAST_CALL_ET< 60 THEN LAST_CALL_ET || ' Seconds'
WHEN LAST_CALL_ET< 3600 THEN ROUND(LAST_CALL_ET/60) || ' Minutes'
WHEN LAST_CALL_ET< 86400 THEN ROUND(LAST_CALL_ET/60/60,1) || ' Hour(s)'
ELSE
ROUND(LAST_CALL_ET/60/60/24,1) || ' Day(s)'
END INACTIVE_TIME
FROM
V$SESSION S, V$PROCESS P
WHERE
S.PADDR=P.ADDR AND
S.STATUS = 'INACTIVE'
-- To find out all session that are inactive more than 1 hour
-- AND S.LAST_CALL_ET >= 3600
ORDER BY LAST_CALL_ET DESC;
SID SERIAL# DATABASE_USER LOGON_TIME STATUS MACHINE PORT PROGRAM INACTIVE_TIME
---------- ---------- -------------------- -------------------- -------- -------------------- ---------- ---------------------------------------- ---------------
1003 31513 STARLIMS_DICT_PROD 12-AUG-2020 05:13:17 INACTIVE APPS\S000AS-DBPILOT 55507 Starlims.Server.WorkerProcess.exe 10.6 Hour(s)
1072 47741 STARLIMS_DATA 12-AUG-2020 05:15:22 INACTIVE APPS\S000AS-DBPILOT 55552 Starlims.Server.WorkerProcess.exe 10.6 Hour(s)
420 46157 STARLIMS_DICT_PROD 12-AUG-2020 05:15:22 INACTIVE APPS\S000AS-DBPILOT 55555 Starlims.Server.WorkerProcess.exe 10.6 Hour(s)
511 57825 STARLIMS_DICT_PROD 12-AUG-2020 05:27:26 INACTIVE APPS\S000AS-DBPILOT 55693 Starlims.Server.WorkerProcess.exe 10.4 Hour(s)
28 16415 STARLIMS_DATA 12-AUG-2020 05:27:27 INACTIVE APPS\S000AS-DBPILOT 55700 Starlims.Server.WorkerProcess.exe 10.4 Hour(s)
765 60569 STARLIMS_DICT_PROD 12-AUG-2020 05:28:33 INACTIVE APPS\S000AS-DBPILOT 55724 Starlims.Server.WorkerProcess.exe 10.3 Hour(s)
726 35357 STARLIMS_DATA 12-AUG-2020 05:28:33 INACTIVE APPS\S000AS-DBPILOT 55721 Starlims.Server.WorkerProcess.exe 10.3 Hour(s)
.. OTHER ROWS HERE
390 rows selected.
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
12-AUG-20 03.48.11.313046 PM +03:00
The INACTIVE_TIME column shows how long a session is idle. In this example, all sessions are idle more than 10 hours.
SOLUTION
This feature can be enabled through the resource parameter IDLE_TIME in a user profile.
IDLE_TIME Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit.
To do so perform the following steps
1) As prerequesite you must enable resource limits by the setting the initialization parameter RESOURCE_LIMIT to TRUE. It can be done dynamically. By default it’s disabled.
SQL> sho parameter RESOURCE_LIMIT
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
resource_limit boolean FALSE
SQL> alter system set RESOURCE_LIMIT=TRUE scope=BOTH;
System altered.
SQL> sho parameter RESOURCE_LIMIT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
2) Find the default profile for a user. For instance, in this example the user is STARLIMS_DATA.
COL USERNAME FOR A15
COL PROFILE FOR A15
SELECT USERNAME,PROFILE FROM DBA_USERS WHERE USERNAME='STARLIMS_DATA';
USERNAME PROFILE
--------------- ---------------
STARLIMS_DATA DEFAULT
3) Get current value of the IDLE_TIME parameter of the user profile (DEFAULT)
COL RESOURCE_NAME FOR A20
COL LIMIT FOR A10
SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='IDLE_TIME';
RESOURCE_NAME LIMIT
-------------------- ----------
IDLE_TIME UNLIMITED
In the DEFAULT profile there is no limit for an idle session. So a session can be connected to a database “forever”.
4) Modify the IDLE_TIME and set it to a desired value. In this example I’m going to set the IDLE_TIME to 8 hours (480 minutes).
SQL> ALTER PROFILE DEFAULT LIMIT IDLE_TIME 480;
Profile altered.
SQL> SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='IDLE_TIME';
RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
IDLE_TIME 480
NOTE: The DEFAULT profile is assigned to all users by default when they are created if you don’t specify the profile explicitly when creating a user.
It means that a modified value of the IDLE_TIME parameter in the DEFAULT profile will be applied automatically to all users having the same profile.
If you need to modify the IDLE_TIME for specific user(s) only then create another profile and assign it as default to that user(s)
From now on, the resource limit of 480 minutes will be applied automatically to all new connected sessions (users) having the DEFAULT profile as default one.
5) Optional. The IDLE_TIME is applied to new sessions only. So, you must disconect existing idle sessions manually. You can generate kill statements by the following query
SET LINES 300
SET PAGES 999
COL SESSIONS_TO_KILL FOR A50
SELECT 'ALTER SYSTEM KILL SESSION ' || q'[']' || SID || ',' || SERIAL# || q'[' IMMEDIATE;]' SESSIONS_TO_KILL
FROM
V$SESSION
WHERE
STATUS='INACTIVE' AND
USERNAME IS NOT NULL AND
LAST_CALL_ET > 60*60*8
ORDER BY LAST_CALL_ET DESC;
SESSIONS_TO_KILL
--------------------------------------------------
ALTER SYSTEM KILL SESSION '1003,31513' IMMEDIATE;
ALTER SYSTEM KILL SESSION '1072,47741' IMMEDIATE;
ALTER SYSTEM KILL SESSION '420,46157' IMMEDIATE;
ALTER SYSTEM KILL SESSION '511,57825' IMMEDIATE;
.. OTHER ROWS HERE
27 rows selected.
In this example all sessions that are inactive more than 8 hours (60*60*8) are going to be killed.
GOOD TO KNOW
If an inactive session exceeds the IDLE_TIME session resource limit, then the database rolls back the current session’s transaction and ends the session. When the session is ended by the database the session’s status is changed to SNIPED.
The SNIPED session is being kept in the v$session view until the session tries to execute any SQL statement. When it happens the database returns an error and then the SNIPED session is cleared from the v$session view.
This is what Oracle Docs says about the SNIPED status:
(**) Why Does A SNIPED Session Not Disappear From V$Session ? (Doc ID 1553143.1) a SNIPPED session is
SNIPED session are the same as KILLED sessions, the entry in the V$SESSION only gets removed after the session which was sniped try to execute any SQL statement
(**) Sessions with Status as KILLED or SNIPED in V$SESSION (Doc ID 2471227.1)
This is a behavior by design. SNIPED and KILLED sessions will remain in v$session so that it can send a notification when the session is reused by end user. Sniped means already killed. But the session will be cleared from v$session as soon the the end user attempts to do any action (any Query), he/she will be notified that his/her session has been terminated as it is timed out and then the session from v$session will be removed.
For instance
COL USERNAME FOR A10
COL STATUS FOR A10
COL EVENT FOR A30
SET PAGES 999
SET LINES 300
SELECT USERNAME,
SID,
SERIAL#,
STATUS,
LAST_CALL_ET SECONDS_ELAPSED,
EVENT
FROM
V$SESSION
WHERE
USERNAME IS NOT NULL;
USERNAME SID SERIAL# STATUS SECONDS_ELAPSED EVENT
---------- ---------- ---------- ---------- --------------- ------------------------------
SYS 6 15529 ACTIVE 0 SQL*Net message to client
TEST 781 15967 SNIPED 1201 SQL*Net message from client
TEST 1161 8167 SNIPED 1427 SQL*Net message from client
TEST 1930 15031 SNIPED 1503 SQL*Net message from client
To automatically disconnect such SNIPED sessions a simple job can be utilized
-- A procedure to disconect SNIPED sessions
CREATE OR REPLACE PROCEDURE SYS.DISCONNECT_SNIPED AS
BEGIN
FOR SESSION IN (
SELECT SID,
SERIAL#
FROM
V$SESSION
WHERE
STATUS='SNIPED' AND
USERNAME IS NOT NULL
-- [Optional].
-- Only sessions that are in SNIPED status
-- longer than 1 hour (or whatever you want)
-- AND LAST_CALL_ET > 3600
)
LOOP
EXECUTE IMMEDIATE Q'[ALTER SYSTEM KILL SESSION ']' || SESSION.SID || ',' || SESSION.SERIAL# || Q'[' IMMEDIATE]';
END LOOP;
END;
/
-- A job to run the procedure on regular basis
SET SERVEROUTPUT ON
VARIABLE JOB_NUMBER NUMBER;
BEGIN
DBMS_JOB.SUBMIT(JOB => :JOB_NUMBER,
WHAT => 'SYS.DISCONNECT_SNIPED;',
NEXT_DATE => SYSDATE, -- Run now
INTERVAL => 'SYSDATE +1/24'); -- Execute every hour
COMMIT;
END;
/
From now on the created job disconnects all such SNIPED session every hour.
To find the created job
SET PAGES 999
SET LINES 300
COL JOB FOR 9999999
COL WHAT FOR A25
COL SCHEMA_USER FOR A15
COL NEXT_RUN_DATE FOR A20
COL INTERVAL FOR A18
COL FAILURES FOR 999999999
SELECT JOB,
SCHEMA_USER,
WHAT,
TO_CHAR(NEXT_DATE,'DD-MON-YYYY HH24:MI:SS') NEXT_RUN_DATE,
INTERVAL,
FAILURES
FROM
DBA_JOBS
WHERE
WHAT LIKE '%DISCONNECT_SNIPED%';
JOB SCHEMA_USER WHAT NEXT_RUN_DATE INTERVAL FAILURES
-------- --------------- ------------------------- -------------------- ------------------ ----------
21 SYS SYS.DISCONNECT_SNIPED; 16-OCT-2020 17:05:56 SYSDATE +1/24 0
Addtional commands to manage the job:
If you wish to run the purge job now
-- EXEC DBMS_JOB.RUN(21);
If you wish to reschedule the purge job, for instance run every 12 hours
-- EXEC DBMS_JOB.INTERVAL(21, 'sysdate+12/24');
If you wish to remove the purge job
-- EXEC DBMS_JOB.REMOVE(21);
REFERENCES
How does the profile parameter ‘idle_time’ work?
Why Does A SNIPED Session Not Disappear From V$Session ? (Doc ID 1553143.1)
Sessions with Status as KILLED or SNIPED in V$SESSION (Doc ID 2471227.1)
Database : 11.2.0.4