Automatic Disconnect Ideal Session

How to automatic disconnect Ideal sessions or INACTIVE Sessions ?

FAQs.

What are Inactive session ?

These are sessions that remain connected to the database with a status in v$session of INACTIVE.

For example,

    - A user starts a program/session, then leaves it running and idle for an extended period of time.

Q. How to overcome this situation?

Ans. Follow below steps to resolve the issue.

Step 1: Enable the feature

Set RESOURCE_LIMIT = TRUE in the database startup parameter file (spfile or pfile)

Example:

- alter system set resource_limit = true scope=spfile;

Step 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;

Step 3. Attach the profile to desired user:

- alter user user_name profile profile_name;

Example:

-        alter user scott profile idletime;

-         

Above example shows that if user session have been inactive for greater than no_of_minutes i.e. 10 min in example, to be disconnected from the database.

Q. What happens if transactions are uncommitted?

Ans. Any uncommitted transactions will be rolled back.

Q. Will user get any notification or error once it disconnected?

Ans. Yes, Next time when user enters any command, user will receive and ORA-02396 exceeded maximum idle time, please connect again.

Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

TAG :  ORA-02396 , inactive session , ideal session