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:
Post a Comment