Tuesday, August 28, 2012

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>

No comments: