Setting up IDEL_TIME on Oracle Database

In this blog we will see how we can setup the IDEL_TIME parameter on Oracle database.

Why we need to set IDLE_TIME ?

When we need to control the inactive time for any sessions in Oracle, we need to define the time in minutes.

Before making any changes to the parameter you need to verify the system parameter i.e. “resource_limit”

Login as “SYS” User and check the parameter value, if you Parameter value is set to “FALSE” then alter the parameter to “TRUE”.

show parameter resource_limit

NAME           TYPE    VALUE 
-------------- ------- ----- 
resource_limit boolean FALSE  

Now let us change the parameter to TRUE.

alter system set resource_limit=TRUE scope=both;

Now let us check if it changed.

show parameter resource_limit

NAME           TYPE    VALUE 
-------------- ------- ----- 
resource_limit boolean TRUE  

Now we are ready to move to next step for setting up the parameter values.


SQL> select username, profile from dba_users
where username ='PARTITION_TEST' ;

Now let us see what is the value present under DEFAULT profile.

SQL> select * from dba_profiles
where profile='DEFAULT'
and resource_name ='IDLE_TIME';

We can see the default value for “IDLE_TIME” is set to “UNLIMITED”. Now we can change the value from default value to 120 which means the system will eliminate all the inactive session that are present in idle state.

SQL> ALTER PROFILE “DEFAULT” LIMIT IDLE_TIME 120;

The best way is to create another profile with all your required setting and assign the same to your user.

Here is one of the sample for creating a profile and assigning it to the user. We can modify any setting at any point of time, here I am leaving all the setting as default.

SQL> CREATE PROFILE "MY_CUSTOM_PROF" LIMIT
CPU_PER_SESSION DEFAULT
CPU_PER_CALL DEFAULT
CONNECT_TIME DEFAULT
IDLE_TIME 120
SESSIONS_PER_USER DEFAULT
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL DEFAULT
PRIVATE_SGA DEFAULT
COMPOSITE_LIMIT DEFAULT
PASSWORD_LIFE_TIME DEFAULT
PASSWORD_GRACE_TIME DEFAULT
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_VERIFY_FUNCTION DEFAULT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 120;


----------------------------------------
Profile "MY_CUSTOM_PROF" created.


SQL> alter user PARTITION_TEST profile MY_CUSTOM_PROF;

User PARTITION_TEST altered.

SQL>select * from dba_profiles
where profile='MY_CUSTOM_PROF'
and resource_name ='IDLE_TIME';
select * from dba_profiles
where profile='MY_CUSTOM_PROF'
and resource_name ='IDLE_TIME';
SQL> select username, profile from dba_users
where username ='PARTITION_TEST' ;

Now my user “PARTITON_TEST” is assigned with the new profile that was created and the time to eliminate the inactive session are set to 120 mins.

Thank you for reading my blog, please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hearing from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.