How to Clearing Down Old Database Sessions

Occasionally you will encounter situations where your database has ever increasing numbers of database sessions. In some cases these may be large numbers of inactive sessions. This article explains how you might manage large numbers of old or inactive database sessions.

Fix the Problem

Large numbers of long term inactive sessions are typically caused by problems with an application or an application server not handling its connections properly. The obvious thing to do is correct the problem at the source. If that is not possible, you can consider one of the options presented below.

Use a Profile

The best way to control the lifespan of a session is to use a database profile. Profiles come with two time-based resource limits.

• CONNECT_TIME : The maximum elapsed time in minutes for a session, whether it is active or not.
• IDLE_TIME : The maximum period of continuous inactive time in minutes.

Sessions that exceed these resource limits are killed. Here are some examples, along with an example of assigning a profile to a user.

-- Kill sessions older than 2 hours.
CREATE PROFILE old_session_profile LIMIT
  CONNECT_TIME 120
/

-- Kill sessions that have been inactive for 1 hour.
CREATE PROFILE inactive_session_profile LIMIT
  IDLE_TIME 60
/

-- Kill sessions older than 2 hours or inactive for 1 hour.
CREATE PROFILE old_or_inactive_sess_profile LIMIT
  CONNECT_TIME 120
  IDLE_TIME 60
/

-- Assign relevant profile to a user.
ALTER USER my_user PROFILE old_or_inactive_sess_profile;

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 15 14:13:57 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 CNDBAPDB                       READ WRITE YES
         6 CNDBAPDB3                      MOUNTED
         7 CNDBAPDB2                      MOUNTED
         8 CNDBAPDB4_FRESH                MOUNTED
         9 CNDBAPDB6                      MOUNTED
SQL> alter session set container=PDB1;

Session altered.

SQL> create profile old_session_profile limit connect_time 120
  2  /

Profile created.

SQL> -- kill sessions that have been inactive for 1 hour.
SQL> create profile inactive_session_profile limit idle_time 60
  2  /

Profile created.

SQL> -- kill sessions older than 2 hours or inactive for 1 hour.
SQL> CREATE PROFILE old_or_inactive_sess_profile LIMIT
  2  CONNECT_TIME 120
  3  IDLE_TIME 60
  4  /

Profile created.

SQL> -- Assign relevant profile to a user.
SQL> ALTER USER maxwellpan PROFILE old_or_inactive_sess_profile;

User altered.

SQL> 
SQL> 
SQL> DROP profile old_or_inactive_sess_profile;
DROP profile old_or_inactive_sess_profile
*
ERROR at line 1:
ORA-02382: profile OLD_OR_INACTIVE_SESS_PROFILE has users assigned, cannot drop
without CASCADE


SQL> select profile from dba_users where username = 'maxwellpan';

no rows selected

SQL> show user;
USER is "SYS"
SQL> show con_name;

CON_NAME
------------------------------
PDB1
SQL> select profile from dba_users where username = 'MAXWELLPAN';

PROFILE
--------------------------------------------------------------------------------
OLD_OR_INACTIVE_SESS_PROFILE

SQL> Drop profile OLD_OR_INACTIVE_SESS_PROFILE cascade;

Profile dropped.

SQL> select profile from dba_users where username = 'MAXWELLPAN';

PROFILE
--------------------------------------------------------------------------------
DEFAULT

SQL>

Be careful to assign the profiles to only those users that are relevant.

 

Use a Job

Profiles work well if you want to affect all sessions connected to a specific user, but sometimes you may need a finer level of control. If you can identify the problem sessions using SQL and/or PL/SQL, you can easily write a procedure to kill the problem sessions and call that procedure from a database job.

Create a user with privilege to query and kill sessions. Notice the user does not have CREATE SESSION privilege and it is locked. We never want anyone to log into this user.

SQL> 
SQL> show user;
USER is "SYS"
SQL> show con_name;

CON_NAME
------------------------------
PDB1
SQL> conn sys@PDB1 as sysdba
Enter password: 
Connected.
SQL> show user;
USER is "SYS"
SQL> CREATE  USER admin_task_user IDENTIFIED BY admin_task_user;

User created.

SQL> GRANT ALTER SYSTEM TO admin_task_user;

Grant succeeded.

SQL> GRANT SELECT ON V_$session To admin_tasks_user;
GRANT SELECT ON V_$session To admin_tasks_user
                              *
ERROR at line 1:
ORA-01917: user or role 'ADMIN_TASKS_USER' does not exist


SQL> GRANT SELECT ON V_$session To admin_task_user;

Grant succeeded.

SQL> ALTER USER admin_task_user ACCOUNT LOCK;

User altered.

SQL> 

Create a procedure to identify and kill problem sessions. Obviously, this is a simple example, but you could write something more complicated if necessary. Make sure the user who needs to call the procedure has execute privilege on it.

CREATE OR REPLACE PROCEDURE admin_task_user.kill_old_report_sessions AS
BEGIN
  FOR cur_rec IN (select 'alter system kill session ''' || sid || ',' || serial# || '''' AS ddl
                  from   v$session
                  where  LOWER(module) = 'financial reports'
                  and    username = 'REPORTS_USER'
                  and    logon_time < SYSDATE-(1/24)
                 )
  LOOP
    BEGIN
      EXECUTE IMMEDIATE cur_rec.ddl;
    EXCEPTION
      WHEN OTHERS THEN
        -- You probably need to log this error properly here.
        -- I will just re-raise it.
        RAISE;
    END;
  END LOOP;
END;
/

GRANT EXECUTE ON admin_task_user.kill_old_report_sessions TO maxwellpan;

SQL> 
SQL> show user;
USER is "SYS"
SQL> show con_name;

CON_NAME
------------------------------
PDB1
SQL> conn sys@PDB1 as sysdba
Enter password: 
Connected.
SQL> 
SQL> CREATE OR REPLACE PROCEDURE admin_task_user.kill_old_report_sessions AS
  2  BEGIN
  3    FOR cur_rec IN (select 'alter system kill session ''' || sid || ',' || serial# || '''' AS ddl
  4                    from   v$session
  5                    where  LOWER(module) = 'financial reports'
  6                    and    username = 'REPORTS_USER'
  7                    and    logon_time < SYSDATE-(1/24)
  8                   )
  9    LOOP
 10      BEGIN
 11        EXECUTE IMMEDIATE cur_rec.ddl;
 12      EXCEPTION
 13        WHEN OTHERS THEN
 14          -- You probably need to log this error properly here.
 15          -- I will just re-raise it.
 16          RAISE;
 17      END;
 18    END LOOP;
 19  END;
 20  /

Procedure created.

SQL> 
SQL> GRANT EXECUTE ON admin_task_user.kill_old_report_sessions TO maxwellpan;

Grant succeeded.

SQL> 

 Killing the wrong session could result in you crashing the database, so be really careful how you identify the problem sessions.

 Test a call to the procedure from the test user. This should run without error. If there are any sessions matching the problem criteria, they will be killed.

SQL> 
SQL> conn maxwellpan/maxwellpan@PDB1;
Connected.
SQL> show user;
USER is "MAXWELLPAN"
SQL> EXEC admin_task_user.kill_old_report_sessions;

PL/SQL procedure successfully completed.

SQL> 

Create a job to call the stored procedure at regular intervals. In this case it runs every 15 minutes.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'maxwellpan.kill_old_report_sessions_job',
    comments        => 'Kill old reports if they have been running for longer than 1 hour.',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN admin_task_user.kill_old_report_sessions; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0,15,30,45; bysecond=0;',
    enabled         => TRUE);
END;
/

The procedure will now manage your problem sessions on a regular basis.