8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 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.
Related articles.
- Killing Oracle Sessions (ALTER SYSTEM KILL / DISCONNECT SESSION)
- ALTER SYSTEM CANCEL SQL : Cancel a SQL Statement in a Session in Oracle Database 18c
- Scheduler (DBMS_SCHEDULER) in Oracle Database
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:
- Killing Oracle Sessions (ALTER SYSTEM KILL / DISCONNECT SESSION)
- Scheduler (DBMS_SCHEDULER) in Oracle Database
- CREATE PROFILE
Hope this helps. Regards Tim...