PROBLEM:

Sql query failed with ORA-02394: exceeded session limit on IO usage error.

SOLUTION:

This error comes, when the sessions exceeds the value of LOGICAL_READS_PER_SESSION defined in user profile. To fix it , We need to increase the value of LOGICAL_READS_PER_SESSION in the user profile.

Let’s reproduce this error.

1. Get the current value of LOGICAL_READS_PER_SESSION in user profile

select a.username,b.PROFILE,b.RESOURCE_NAME,b.limit from dba_users a , dba_profiles b where a.profile=b.profile and b.RESOURCE_NAME=’LOGICAL_READS_PER_SESSION’ and a.username=’&USER_NAME’;

The value is set to 23000.

2. Connect to the user and run some query:

3. Check the logical reads already done by the current session:

So out of allowed 23000, it has already used 20376. So If I run the query again, it will exceed the allowed value, throw an error.

4. To fix it , Increase the value of LOGICAL_READS_PER_SESSION IN USER PROFILE.