Automated Idle Session Termination in Oracle 10g Using PL/SQL

Managing inactive database sessions is a critical responsibility for Database Administrators (DBAs). In Oracle 10g environments, long-idle sessions can consume server resources, hold locks unintentionally, exhaust process limits, and impact overall database performance.

Problem Statement

Idle sessions accumulate due to application connection leaks, improper connection pool configuration, users closing applications without logging out, or network interruptions. Manual termination is inefficient in production systems.

Identifying Idle Sessions

Example query to identify idle sessions older than 15 minutes:

SELECT sid,
       serial#,
       username,
       trunc(last_call_et/3600,2)||' hr' last_call_et
FROM   v$session
WHERE  status='INACTIVE'
AND    wait_class='Idle'
AND    last_call_et > 900
AND    username IS NOT NULL;

Solution Overview

The implemented solution performs the following tasks:
• Identifies inactive sessions older than a defined threshold
• Excludes critical system users
• Kills qualifying sessions
• Logs each terminated session
• Maintains log retention

Step 1: Create Logging Table

CREATE TABLE kill_idle_sessions_log (
    log_id     NUMBER,
    username   VARCHAR2(30),
    sid        NUMBER,
    serial_no  NUMBER,
    killed_at  DATE
);

Step 2: Create Sequence

CREATE SEQUENCE kill_idle_sessions_log_seq
START WITH 1
INCREMENT BY 1
NOCACHE;

Step 3: Create Procedure

CREATE OR REPLACE PROCEDURE kill_idle_sessions (
    p_idle_seconds IN NUMBER DEFAULT 900
)
IS
BEGIN

   DELETE FROM kill_idle_sessions_log
   WHERE killed_at < SYSDATE - 7;

   FOR r IN (
      SELECT sid, serial#, username
      FROM   v$session
      WHERE  status = 'INACTIVE'
      AND    wait_class = 'Idle'
      AND    last_call_et > p_idle_seconds
      AND    username IS NOT NULL
      AND    type != 'BACKGROUND'
      AND    username NOT IN (
             'SYS','SYSTEM'
      )
   )
   LOOP
      BEGIN
         EXECUTE IMMEDIATE
            'ALTER SYSTEM KILL SESSION ''' ||
            r.sid || ',' || r.serial# || ''' IMMEDIATE';

         INSERT INTO kill_idle_sessions_log
         VALUES (
            kill_idle_sessions_log_seq.NEXTVAL,
            r.username,
            r.sid,
            r.serial#,
            SYSDATE
         );

      EXCEPTION
         WHEN OTHERS THEN
            NULL;
      END;
   END LOOP;

   COMMIT;

END;
/

How to Execute

BEGIN
   kill_idle_sessions;
END;
/

To run with a 30-minute threshold:

BEGIN
   kill_idle_sessions(1800);
END;
/

Best Practices

• Never terminate SYS or SYSTEM sessions.
• Avoid committing inside loops.
• Maintain log retention to prevent uncontrolled growth.
• Consider scheduling with DBMS_JOB for automation.

Conclusion

Automating idle session termination in Oracle 10g improves resource utilization, system stability, and DBA efficiency. While Oracle 10g is legacy software, proper procedural automation can maintain operational stability.