Disconnect Idel Sessions

We may reach maximum number of sessions even though there are many sessions which are ‘INACTIVE’ for quite a long time. We can set timeout for such idle sessions by setting IDLE_TIME of user’s profile.

Please note:

a) the resource limit has no effect to sessions which had been connected before the new resource limit was assigned to user. It has effect on ONLY NEW CONNECTION.

b) pmon will will clean up resources and locks owned by expired idle session. However in early release of 9i it doesn’t kid off the session rather it will marks it as ‘SNIPPED’ in expectation user will receive error message “ORA-02396: exceeded maximum idle time, please connect again” when he is trying to perform operations again. However it’s still possible to reach maximum number session error! This has been changed in late release of 9i and upper, the expired session and its resources and locks will be removed immediately by pmon.

1) Set recource_limt init parameter

The resource_limit parameter must be set to ‘true’ (default is false) to enable resource limits by profile. Please note, resource_limit has no effect on password limit, they are always enabled.

alter system set resource_limit=true;

2) Create a profile with IDLE_TIME limit and assign it to user

create profile idle_timeout_60min limit IDLE_TIME 60;

alter user scott profile idle_timeout_60min;

3) verification

— in one session connect as scott user

sqlplus scott/tiger

select * from DEPT for update;

— in another session connect as system user

sqlplus system/manager

select username, status, program, machine from
v$session where status='INACTIVE' and username = 'SCOTT';

SELECT s.username,  s. status sess_status, t.status trans_status
FROM v$session s, v$transaction t
WHERE     s.status = 'INACTIVE'
AND username = 'SCOTT
and t.ses_addr = s.saddr;

exec dbms_lock.sleep(timeout_in_secs);   -- eg. 60 secs

select username, status, program, machine from
v$session where and username = 'SCOTT';

SELECT s.username,  s. status sess_status, t.status trans_status
FROM v$session s, v$transaction t
AND username = 'SCOTT
and t.ses_addr = s.saddr;

I tested the procedure against Oracle 9.2.0.8, the expired session disappeared and all its resources allocated were released by pmon.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: