Home » RDBMS Server » Server Administration » Kill IDLE sessions after time out (merged)
Kill IDLE sessions after time out (merged) [message #349189] Fri, 19 September 2008 04:08 Go to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Hi,

What i am trying to do is a oracle session should be killed if the session is IDLE for 5 mins i.e if the user doesnt perform any operation in the session for 10 mins, the session should be killed.

For this i have created a profile DEV_PROFILE and assigned to a user.

For testing purpose, I am connecting to the user through SQL*Plus and leave the sessinon IDLE for 10 minutes. I expect that sessoin should have been killed after 5 minutes of idle time.

But even after 10 mins I am able to execute queries from that session.d

Let me know whether what i have done is the correct approach to kill IDLE sessions.

Also kindle help to get this issue solved.

Quote:
SQL> select * from dba_profiles where profile='DEV_PROFILE';

PROFILE RESOURCE_NAME RESOURCE_T LIMIT
-------------- ------------------------- ---------- ----------
DEV_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
DEV_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
DEV_PROFILE CPU_PER_SESSION KERNEL DEFAULT
DEV_PROFILE CPU_PER_CALL KERNEL DEFAULT
DEV_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
DEV_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
DEV_PROFILE IDLE_TIME KERNEL 5
DEV_PROFILE CONNECT_TIME KERNEL 5
DEV_PROFILE PRIVATE_SGA KERNEL DEFAULT
DEV_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEV_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
DEV_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
DEV_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
DEV_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
DEV_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
DEV_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT




Thanks,
GOKUL
Kill IDLE sessions after time out. [message #349190 is a reply to message #349189] Fri, 19 September 2008 04:10 Go to previous messageGo to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Hi,

What i am trying to do is a oracle session should be killed if the session is IDLE for 5 mins i.e if the user doesnt perform any operation in the session for 10 mins, the session should be killed.

For this i have created a profile DEV_PROFILE and assigned to a user.

For testing purpose, I am connecting to the user through SQL*Plus and leave the sessinon IDLE for 10 minutes. I expect that sessoin should have been killed after 5 minutes of idle time.

But even after 10 mins I am able to execute queries from that session.d

Let me know whether what i have done is the correct approach to kill IDLE sessions.

Also kindle help to get this issue solved.

Quote:
SQL> select * from dba_profiles where profile='DEV_PROFILE';

PROFILE RESOURCE_NAME RESOURCE_T LIMIT
-------------- ------------------------- ---------- ----------
DEV_PROFILE COMPOSITE_LIMIT KERNEL DEFAULT
DEV_PROFILE SESSIONS_PER_USER KERNEL DEFAULT
DEV_PROFILE CPU_PER_SESSION KERNEL DEFAULT
DEV_PROFILE CPU_PER_CALL KERNEL DEFAULT
DEV_PROFILE LOGICAL_READS_PER_SESSION KERNEL DEFAULT
DEV_PROFILE LOGICAL_READS_PER_CALL KERNEL DEFAULT
DEV_PROFILE IDLE_TIME KERNEL 5
DEV_PROFILE CONNECT_TIME KERNEL 5
DEV_PROFILE PRIVATE_SGA KERNEL DEFAULT
DEV_PROFILE FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEV_PROFILE PASSWORD_LIFE_TIME PASSWORD DEFAULT
DEV_PROFILE PASSWORD_REUSE_TIME PASSWORD DEFAULT
DEV_PROFILE PASSWORD_REUSE_MAX PASSWORD DEFAULT
DEV_PROFILE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
DEV_PROFILE PASSWORD_LOCK_TIME PASSWORD DEFAULT
DEV_PROFILE PASSWORD_GRACE_TIME PASSWORD DEFAULT




Thanks,
GOKUL
Re: Kill IDLE sessions after time out (merged) [message #349198 is a reply to message #349189] Fri, 19 September 2008 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The session will be killed when the user will try to execute his/her next action.

Regards
Michel
Re: Kill IDLE sessions after time out (merged) [message #349201 is a reply to message #349198] Fri, 19 September 2008 05:15 Go to previous messageGo to next message
gokul_ifs
Messages: 41
Registered: March 2006
Location: india
Member
Assign the below mentioned profile to the user GEETA:

15:20:04 SQL> alter user geeta profile dev_profile;

User altered.

15:20:08 SQL>


Connect to user GEETA in another session:

D:\DB\PhaseII\scripts\UGC\New_UGC_Structure>sqlplus geeta@bobjecms

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Sep 19 15:22:07 2008

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set time on
15:22:13 SQL>


15:38:46 SQL> select count(1) from upr_user_master;

COUNT(1)
----------
0


Above i am connecting to the user GEETA and kept the session IDLE for 16 minutes.

After 16 mins, when i execute the query, it returns the result.

But, according to the profile set the session should be killed if the user is idle for 5 minutes.

Please correct me if my understanding is wrong.

Thanks,
GOKUL
Re: Kill IDLE sessions after time out (merged) [message #349215 is a reply to message #349201] Fri, 19 September 2008 06:09 Go to previous message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Silly question but did you set resource_limit parameter to true?

Regards
Michel
Previous Topic: No Archivelog are being produced
Next Topic: Missing in oracle10g OEM (merged)
Goto Forum:
  


Current Time: Tue Dec 06 02:25:28 CST 2016

Total time taken to generate the page: 0.08225 seconds