ORA-02396 tips

Getting an ORA-02396 error when we set a resource profile.

Sample :

SQL> alter system set resource_limit = true;

System altered.

SQL> create profile time_out_profile limit idle_time 1;

Profile created.

SQL> create user test_idle_time identified by test profile time_out_profile;

User created.

SQL> grant connect to test_idle_time;

Grant succeeded.

SQL> connect test_idle_time/test
Connected.

SQL> set timing on

01:39:12 SQL> select username from user_users;

USERNAME
------------------------------
TEST_IDLE_TIME

-- **********************************************************
-- We have now waited longer than the idle_time (one minute)
-- **********************************************************
01:43:14 SQL> select username from user_users;

select username from user_users
*
ERROR at line 1:
ORA-02396: exceeded maximum idle time, please connect again

How do I get rid of the ORA-02396 error?  Is there an unlimited value for idle_time?

Answer:  The ORA-02396 error is thrown due to exceeding the idle_time setting on the database server.  This error happens when you have a resource profile enabled and you should enable resource_limit and create a profile whose idle_time will be limited (in minutes).
If you omit a resource profile then you will have an unlimited idle_time and you will never get an ORA-02396 error.

Note that Oracle periodically tests whether the connection is still active, and if it is inactive, you will get an ORA-02396 error, provided that the user resource profile is set as shown above.

Resolving the problem involves checking the idle time-out value at the database end (SQL*Plus resource profile), and increase this value of the idle_time.