Print Friendly, PDF & Email

GOAL
There are lots of idle sessions/processes in my database. All inactive(Idle) sessions must be disconnected automatically after 8 hours since they have become inactive(idle).

SYMPTOMS
The following statement shows IDLE time for all INACTIVE sessions in a database

SET LINES 300
SET PAGES 999
COL DATABASE_USER FOR A20
COL LOGON_TIME FOR A25
COL MACHINE FOR A25
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 have been idle for 10 hours so far.

SOLUTION
Automatic disconnection of all IDLE sessions can be achieved by using 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.

This is what happens when a user “forgets” to commit or rollback its transaction and leaves his/her session INACTIVE for a long period of time that exceeded the specified period of time for the IDLE_TIME resource limit.

Oracle Database enforces resource limits in the following ways:

If a user exceeds the CONNECT_TIME or IDLE_TIME session resource limit, then the database rolls back 
the current transaction and ends the session. When the user process next issues a call, 
the database returns an error.
To enable IDLE_TIME resource limit

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/users. 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 resource parameter of the 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 profile named DEFAULT there is no limit for sessions that are in IDLE status. So, such sessions can exist in a database infinitely.

 
4) Modify the IDLE_TIME resource parameter and set it to a desired value. In this example I’m about to set it up 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 profile DEFAULT is assigned to all users by default if you don’t specify the profile explicitly when creating a user. It simply means that a modified value of the IDLE_TIME resource parameter in the profile DEFAULT will be applied automatically to all users sharing the same profile. If you need to modify the IDLE_TIME for specific user(s) only then CREATE ANOTHER DEDICATED PROFILE and assign it as default to that user(s)

 
5) Optional. The IDLE_TIME is applied to new connected sessions only. So, you must disconnect existing IDLE sessions manually.

Use the next query to generate KILL statements

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.

 
IMPORTANT INFORMATION
If an INACTIVE session exceeds the IDLE_TIME session resource limit, then the database rollsbacks the current session’s transaction and ends the session. When the session is ended by the database the status of such ended session is changed to SNIPED.

The SNIPED session is being kept in the V$SESSION view until the ended 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:

(1) Why Does A SNIPED Session Not Disappear From V$Session ? (Doc ID 1553143.1)

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

(2) 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, by querying V$SESSION view I can see some sessions with the status SNIPED

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 job manually

-- EXEC DBMS_JOB.RUN(21);

 
If you wish to reschedule the job, for instance to run every 12 hours

-- EXEC DBMS_JOB.INTERVAL(21, 'sysdate+12/24');

 
If you wish to remove the 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)

 
 

Version  : 15:52 12.08.2020
Database : 11.2.0.4