Kill idle session in Oracle Database

Yes, even an idle session connected to an Oracle Database can still consume and hold onto certain resources. You can ensure that database connections automatically get snipped and cleared by following the process below.

To implement session sniping in Oracle Database, which involves identifying and terminating idle sessions after a certain period of time, you can use the IDLE_TIME parameter along with the RESOURCE_LIMIT parameter. Here's how to do it:

1. Enable Resource Limits:

Before you can use the IDLE_TIME parameter, you must ensure that resource limits are enabled in the database.

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

This command enables the enforcement of resource limits defined in user profiles, including idle time limits.

2. Create or Modify a Profile with IDLE_TIME:

You need to create or modify a profile to set the IDLE_TIME limit. This limit specifies the maximum time (in minutes) a session can remain idle before it is "sniped."

• Create a New Profile with IDLE_TIME:

    CREATE PROFILE my_profile LIMIT
    IDLE_TIME 30; -- 30 minutes
  

• Modify an Existing Profile:

    ALTER PROFILE my_profile LIMIT
    IDLE_TIME 30; -- 30 minutes
  

Here, my_profile is the name of the profile, and IDLE_TIME is set to 30 minutes. After this time, the session will be marked as "sniped."

3. Assign the Profile to Users:

Assign the profile to the users whose sessions you want to manage.

ALTER USER my_user PROFILE my_profile;

Replace my_user with the username to which you are assigning the profile.

4. Understanding Sniped Sessions:

When a session exceeds its IDLE_TIME, Oracle will mark it as "sniped." A sniped session remains in the V$SESSION view with a status of SNIPED. The session is not immediately killed; it remains until the client attempts to interact with the database again. At that point, Oracle will disconnect the session.

• View Sniped Sessions:

    SELECT SID, SERIAL#, STATUS, USERNAME, MACHINE
    FROM V$SESSION
    WHERE STATUS = 'SNIPED';
  

5. Optional: Automatically Kill Sniped Sessions:

If you want to automatically kill sniped sessions without waiting for the client to interact, you can use a script or a database job to identify and terminate sniped sessions.

• Example Script to Kill Sniped Sessions:

    BEGIN
       FOR rec IN (SELECT sid, serial# FROM v$session WHERE status = 'SNIPED') LOOP
           EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || rec.sid || ',' || rec.serial# || ''' IMMEDIATE';
       END LOOP;
    END;
  

You can schedule this script to run periodically using the Oracle Scheduler.

6. Monitor and Adjust:

Regularly monitor the impact of sniping on your system and adjust the IDLE_TIME as necessary. You can also monitor the profile settings to ensure they are being enforced correctly.

Considerations:

• IDLE_TIME Limit: The IDLE_TIME limit is defined in minutes. Setting it too low might cause important sessions to be sniped, disrupting user activities.

• Resource Management: Sniping is particularly useful in environments where you want to free up resources by disconnecting sessions that are no longer active.

By implementing session sniping with IDLE_TIME, you can efficiently manage idle sessions in your Oracle Database, helping to free up resources and maintain system performance.

Database Issues can arise when idle sessions are still connected to the database.