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>