***Checked for relevance on 30-Apr-2012***
- goal: How to automate disconnection of idle sessions
- fact: Oracle Server - Enterprise Edition 8.1
fix:
1. Enable the feature
- alter system set resource_limit = true;
OR
Set the parameter in the parameter file and restart the database.
2. Create the profile for monitoring idle time:
- create profile profile_name limit idle_time no_of_minutes;
Example:
- create profile idletime limit idle_time 10;
3. Attach the profile to desired user:
- alter user user_name profile profile_name;
Example:
- alter user scott profile idletime;
This forces any Oracle user sessions, which have been inactive for greater than
no_of_minutes, to be disconnected from the database. Any uncommitted
transaction will be rolled back. When the idle time has passed, the session
will be suspended. Next time the user enters a command he will receive an ORA-02396: exceeded maximum idle time, please connect again.
Reference:
Oracle8i Administrator's Guide, Chapter 22 Managing Users and Resources