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.

 
 
USEFUL LINK
 
How does the profile parameter ‘idle_time’ work?