How do we kill inactive session in oracle?

This article is witten in oracle9i. It should apply to oracle1og and further release too.

Inactive session
Oracle Marks the session as INACTIVE when the moment, session is ideal. These sessions are remain connected to the database with a status in v$session of INACTIVE. A user starts a program/session, then leaves it running and idle for an extended period of time. Some time, user connect to the database and go for coffee and chat with somebody and come back to desk and check mails. During this time, the session becomes INACTIVE.

Dead Connection
Sometime, user shutdown the machine without logout the database connection or network prevent to connect the database, then connection becomes dead connection. SQL NET detect the dead connection and release the resource. Please refer metalink id (Note:395505.1, Note:151972.1) for more details about DCD(dead connection deduction).

How do we kill the inactive session?

There are couple of ways to kill the inactive session.

Option 1
We can write the script to kill the session. The below script kills any session which is INACTIVE.

SELECT 'ALTER SYSTEM KILL SESSION '''''sid','serial#''''' immediate;'
FROM v$session
WHERE status ='INACTIVE'

In case, if you need to kill all the session which are inactive for last four hours, then the above script is not an option. The above command kills all the session regardless of when it becomes inactive. Next option is best option to kill the session which are inactive for certain period of time.

Option 2
We can disconnect the inactive session through Oracle profile. We can change the idle_time in the user profile. Here are the steps to change the idle_time.

Step1 First we need to change the resource_limit to TRUE. By default, it is FALSE.

SQL> select value from gv$parameter
2 where name='resource_limit';

VALUE
--------------------------------------------------------------------------------
FALSE

SQL> ALTER SYSTEM SET resource_limit=TRUE;
System altered.

Step2 Alter the profile to change the idle_time. Here i am using custom profile named developer.

SQL> ALTER profile developer limit idle_time 240;

Profile altered.

Step 3 If you are not using developer profile for application schema, then assign this profile to application schema.

SQL> alter user app_data profile developer;

User altered.