How to Manage Inactive Sessions in the Oracle 23AI Database: Step-by-Step Guide

Introduction

Manage inactive sessions in Oracle 23AI database can lead to unnecessary resource consumption and reduced performance. Managing these sessions efficiently ensures database stability and improves overall performance. In this guide, we’ll walk you through the best methods to identify, manage, and prevent inactive sessions.

Understanding Inactive Sessions in Oracle 23AI

Inactive sessions are database connections that remain idle without performing transactions. Unlike active sessions that continuously execute queries, inactive sessions stay connected without using resources productively. Common causes include:

Users who forget to log out
Unclosed connections from applications
Long-running transactions stuck in a waiting state

Checking to Manage inactive sessions in Oracle 23AI

1. CONNECT WITH PDB DATABASE(ORACLE 23AI)

[oracle@a2z-23ai ~]$ sqlplus / as sysdba

 SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Sat Jan 25 06:26:57 2025

Version 23.6.0.24.10

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

Connected to:

Oracle Database 23ai EE High Perf Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems

Version 23.6.0.24.10

SQL>

SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 PROD                         READ WRITE NO



SQL> alter session set container=PROD;

Session altered.

2. VERIFY SESSION STATUS

SQL> select USERNAME,STATUS,SCHEMA#,SCHEMANAME,OSUSER,MACHINE,PROGRAM,EVENT from v$session;

3. Using SQL to Kill inactive Sessions

To terminate a session manually, identify the SID and Serial#, then execute:

SQL> select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='INACTIVE';

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'

alter system kill session '16,3670' immediate;

alter system kill session '19,2265' immediate;

SQL > select count(*) from gv$session s, v$process p where p.addr=s.paddr and s.status='INACTIVE';

SQL > select LOGON_TIME from gv$session s, v$process p where p.addr=s.paddr and s.status='INACTIVE';

SQL > select count(s.status) INACTIVE_SESSIONS

from gv$session s, v$process p

where 

p.addr=s.paddr and

s.status='INACTIVE' and last_call_et > 3600;

Create a batch file to save session logs

Here, we will create a batch file to Manage inactive sessions in Oracle 23AI

spool $file;

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v\$session where status='INACTIVE' and program like 'JDBC%' and last_call_et > 3600;

spool off;

@file;

exit;

EOF 

3600sec :- 1 hours

spool $file;

select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v\$session where status='INACTIVE' and program like 'JDBC%' and last_call_et > 3600;

spool off;

@$file;

exit;

EOF

Automating Inactive Session Management

You can schedule periodic session cleanups using Oracle Scheduler. Create a stored procedure to identify and terminate inactive sessions

BEGIN
  DBMS_SCHEDULER.create_job(
    job_name   => 'cleanup_inactive_sessions',
    job_type   => 'PLSQL_BLOCK',
    job_action => 'BEGIN kill_inactive_sessions_proc; END;',
    repeat_interval => 'FREQ=HOURLY; BYMINUTE=0,30;'
  );
END;

Troubleshooting Issues with Inactive Sessions

If you encounter locked or blocked sessions, use: This helps identify blocking sessions for resolution.

SELECT blocking_session, sid, serial# FROM v$session WHERE blocking_session IS NOT NULL;

Efficiently managing inactive sessions in Oracle 23AI ensures optimal database performance and stability. By proactively identifying, limiting, and terminating inactive sessions, you can prevent unnecessary resource consumption and maintain a high-performance environment.