8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

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
Use a Profile
Use a Job

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;

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.

CONN sys@pdb1 as sysdba

CREATE USER admin_tasks_user IDENTIFIED BY MyPassword1;
GRANT ALTER SYSTEM TO admin_tasks_user;
GRANT SELECT ON v_$session TO admin_tasks_user;
ALTER USER admin_tasks_user ACCOUNT LOCK;

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.

CONN sys@pdb1 as sysdba

CREATE OR REPLACE PROCEDURE admin_tasks_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_tasks_user.kill_old_report_sessions TO test;

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.

CONN test/test@pdb1

EXEC admin_tasks_user.kill_old_report_sessions;

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        => 'test.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_tasks_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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.