PROBLEM:

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

SQL> select count(*) from dba_objects;
select count(*) from dba_objects
*
ERROR at line 1:
ORA-02394: exceeded session limit on IO usage, you are being logged off

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’;

SQL> 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';
Enter value for user_name: TEST9
old   1: 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'
new   1: 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='TEST9'

USERNAME     PROFILE             RESOURCE_NAME                    LIMIT
------------ ------------------- -------------------------------- ------------
TEST9        TEST                LOGICAL_READS_PER_SESSION        23000

The value is set to 23000.

2. Connect to the user and run some query:

SQL> show user
USER is "TEST9"
SQL> select count(*) from dba_objects;

  COUNT(*)
----------
    122695

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

SQL> select value from v$sesstat a, v$statname b
  where sid =
  (select distinct sid from v$mystat)
and a.statistic# = b.statistic#
and name = 'session logical reads';  2    3    4    5

     VALUE
----------
     20376

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.

SQL> select count(*) from dba_objects;
select count(*) from dba_objects
*
ERROR at line 1:
ORA-02394: exceeded session limit on IO usage, you are being logged off

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

ALTER PROFILE TEST LIMIT LOGICAL_READS_PER_SESSION  UNLIMITED;