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)