ORA-02391

I tested this on an Oracle 11.1 database.

 

Oracle profiles control how certain database resources are allocated to a user session. They also define some security rules. When you create a user, it is assigned a profile and, if you do not specify it explicitly, the DEFAULT profile will be used:

 

SQL> grant create session to andrew

  2  identified by reid

  3  /

 

Grant succeeded.

 

SQL> select profile from dba_users

  2  where username = 'ANDREW'

  3  /

 

PROFILE

------------------------------

DEFAULT

 

SQL>

 

You can look at the limits defined in a profile by querying DBA_PROFILES:

 

SQL> select profile, resource_name, limit

  2  from dba_profiles

  3  where profile = 'DEFAULT'

  4  and resource_name = 'SESSIONS_PER_USER'

  5  /

 

PROFILE    RESOURCE_NAME        LIMIT

---------- -------------------- ----------

DEFAULT    SESSIONS_PER_USER    UNLIMITED

 

SQL>

 

The SESSIONS_PER_USER resource is set to UNLIMITED so a user with the DEFAULT profile can have as many simultaneous sessions as he wishes. What can you do if you do not like this? You can create a new profile with a different SESSIONS_PER_USER limit and assign it to the user. I will demonstrate this in a future post. Alternatively you can alter the DEFAULT profile, as shown below:

 

SQL> alter profile default

  2  limit sessions_per_user 3

  3  /

 

Profile altered.

 

SQL> select profile, resource_name, limit

  2  from dba_profiles

  3  where profile = 'DEFAULT'

  4  and resource_name = 'SESSIONS_PER_USER'

  5  /

 

PROFILE    RESOURCE_NAME        LIMIT

---------- -------------------- ----------

DEFAULT    SESSIONS_PER_USER    3

 

SQL>

 

You also need to set RESOURCE_LIMIT to TRUE otherwise Oracle does not check limits in a user's profile at all:

 

SQL> alter system set resource_limit = true

  2  /

 

System altered.

 

SQL>

 

The screen print below shows how this works. As usual, click on the image to enlarge it and bring it into focus if necessary. If that doesn't work, use your browser's zoom function. Three simultaneous connections have been made to the database. A fourth connection is then attempted but fails with an ORA-02391:

LOGICAL_READS_PER_SESSION

One of the resources you can limit in a profile is called logical_reads_per_session. According to the Oracle documentation, it is used to:

Specify the permitted number of data blocks read in a session, including blocks read from memory and disk. I decided to try it out in an Oracle 11.1 database.

 

I created a profile called for_andrew, which would limit logical_reads_per_session to 10000:

SQL> conn / as sysdba

Connected.

SQL> create profile for_andrew

  2  limit logical_reads_per_session 10000

  3  /

 

Profile created.

 

SQL>

I set resource_limit to true so that Oracle would enforce the logical_reads_per_session limit:

SQL> alter system set resource_limit = true

  2  /

 

System altered.

 

SQL>

I created a user with the new profile:

SQL> create user andrew

  2  identified by reid

  3  profile for_andrew

  4  /

 

User created.

 

SQL> grant create session,

  2  select any dictionary to andrew

  3  /

 

Grant succeeded.

 

SQL>

I logged in with this user, ran some SQL against dba_tables and counted the session logical reads this had consumed. For the purposes of this simple demonstration, I ignored any overhead which the 2^nd piece of SQL may have incurred:

SQL> conn andrew/reid

Connected.

SQL> select max(last_analyzed) from dba_tables

  2  /

 

MAX(LAST_

---------

18-OCT-15

 

SQL> select value from v$sesstat a, v$statname b

  2  where sid =

  3  (select distinct sid from v$mystat)

  4  and a.statistic# = b.statistic#

  5  and name = 'session logical reads'

  6  /

 

       VALUE

------------

        6936

 

SQL>

I logged in again ran some SQL against dba_indexes and counted the session logical reads this had consumed. Again, I ignored any overhead which may have been incurred by checking the session logical reads figure in v$sesstat:

SQL> conn andrew/reid

Connected.

SQL> select max(last_analyzed) from dba_indexes

  2  /

 

MAX(LAST_

---------

18-OCT-15

 

SQL> select value from v$sesstat a, v$statname b

  2  where sid =

  3  (select distinct sid from v$mystat)

  4  and a.statistic# = b.statistic#

  5  and name = 'session logical reads'

  6  /

 

       VALUE

------------

        7341

 

SQL>

The 2 statements together used over 14000 session logical reads. I guessed that if I tried to run them in the same session, this would exceed the limit set by the logical_reads_per_session parameter in the user’s profile. I logged in again and tried to do this. As expected, the 1^st SQL worked but the 2^nd failed with an ORA-02394:

SQL> conn andrew/reid

Connected.

SQL> select max(last_analyzed) from dba_tables

  2  /

 

MAX(LAST_

---------

18-OCT-15

 

SQL> select max(last_analyzed) from dba_indexes

  2  /

select max(last_analyzed) from dba_indexes

                               *

ERROR at line 1:

ORA-02394: exceeded session limit on IO usage, you are

being logged off

 

SQL>

ORA-02393

This example, which was tested on Oracle 11.2.0.2.7, shows how you can limit the amount of CPU time used by an SQL statement. Before you start, you need to ensure that RESOURCE_LIMIT  is set to TRUE otherwise limits will not be enforced:

SQL> alter system set resource_limit = true

  2  /

 

System altered.

 

SQL>

Then you need to create a profile which will limit CPU per statement to 1 second:

SQL> create profile for_andrew

  2  limit cpu_per_call 100

  3  /

 

Profile created.

 

SQL>

Next you need to create a user who will be assigned this profile and connect to the database with it:

SQL> create user andrew identified by reid

  2  profile for_andrew

  3  /

 

User created.

 

SQL> grant create session,

  2        alter session,

  3        select any dictionary to andrew

  4  /

 

Grant succeeded.

SQL> conn andrew/reid

Connected.

SQL>

After this, you need to run a SQL statement which will use more than 1 second of CPU time. Before doing this, check the session's CPU usage so far:

SQL> select a.value/100

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and b.name = 'CPU used by this session'

  5  /

 

A.VALUE/100

-----------

        .02

 

SQL>

... then execute the statement and wait for it to fail:

SQL> select count(*)

  2  from dba_tables a, dba_tables b

  3  /

from dba_tables a, dba_tables b

     *

ERROR at line 2:

ORA-02393: exceeded call limit on CPU usage

 

SQL>

Once the statement has failed, check that it has only increased the session's CPU usage by 1 second:

SQL> select a.value/100

  2  from v$mystat a, v$sysstat b

  3  where a.statistic# = b.statistic#

  4  and b.name = 'CPU used by this session'

  5  /

 

A.VALUE/100

-----------

       1.34

 

SQL>

ORA-02392

This example shows how you can limit the amount of CPU time consumed by a user session. It was tested on Oracle 11.2. First I created a profile which limits CPU per session to 50 hundredths of a second i.e. 0.5 seconds:

 

SQL> CONN / AS SYSDBA

Connected.

SQL> CREATE PROFILE FOR_ANDREW

  2  LIMIT CPU_PER_SESSION 50

  3  /

 

Profile created.

 

SQL>

 

Next, I created a user and assigned him the profile I had just created:

 

SQL> CREATE USER ANDREW

  2  IDENTIFIED BY REID

  3  PROFILE FOR_ANDREW

  4  /

 

User created.

 

SQL> GRANT CREATE SESSION,

  2        SELECT ANY TABLE,

  3        SELECT ANY DICTIONARY TO ANDREW

  4  /

 

Grant succeeded.

 

SQL>

 

Then I set the RESOURCE LIMIT initialisation parameter to TRUE:

 

SQL> COL RESOURCE_LIMIT FORMAT A30

SQL> SELECT VALUE RESOURCE_LIMIT

  2  FROM V$PARAMETER

  3  WHERE NAME = 'resource_limit'

  4  /

 

RESOURCE_LIMIT

------------------------------

FALSE

 

SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE

  2  /

 

System altered.

 

SQL>

 

Finally, I connected as the user and ran some SQL. Once the allotted amount of CPU time had been used, the user was given an ORA-02392 and logged off:

 

SQL> CONN ANDREW/REID

Connected.

SQL> SELECT COUNT(*) FROM DBA_TABLES

  2  /

 

  COUNT(*)

----------

      2724

 

SQL> SELECT COUNT(*) FROM DBA_INDEXES

  2  /

SELECT COUNT(*) FROM DBA_INDEXES

                     *

ERROR at line 1:

ORA-02392: exceeded session limit on CPU usage, you

are being logged off

 

SQL>

Reply to: Reply to Andrew Reid

Send