Create a profile to kill session automatically in Oracle

Oracle allows you to manage user sessions with profiles. To automatically end a session after 2 hours, set an idle time limit in a profile. Here’s how to do it.

Step 1: Create a Profile with an Idle Timeout

The following setting automatically disconnects users who are idle for 2 hours.

CREATE PROFILE two_hour_limit_profile LIMIT
    IDLE_TIME 120;

Note: IDLE_TIME is specified in minutes. Here, 120 minutes = 2 hours.

Step 2: Assign the Profile to Users

ALTER USER username PROFILE two_hour_limit_profile;

Step 3: Verify the Profile Assignment

SELECT username, profile FROM dba_users WHERE username = 'USERNAME';

Step 4: Monitor Idle Sessions

SELECT sid, serial#, username, status, last_call_et
FROM v$session
WHERE username IS NOT NULL;

Note: last_call_et (Elapsed Time) shows the idle time in seconds.

Step 5: Manually Kill a Session (If Needed)

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;