Using Resource Profiles

I never had the need to use Resource Profiles extensively. Recently, though, I've had the opportunity to investigate this feature.

First things first, the resouce_limit parameter must be set to TRUE. You can either set it in the init.ora or via ALTER SYSTEM.

Next, you create the profile and assign limits to it. Read the descriptions carefully, though, some of the resource parameters may sound self-explanatory, but aren't. For example, you would think SESSIONS_PER_USER would mean the number of times a particular user can login. In fact, it's the number of concurrent sessions that can run at one time.

SQL> create profile really_small limit
2  sessions_per_user 1
3  cpu_per_session   100
4  cpu_per_call      100
5  connect_time       5
6  /

Profile created.

Then you assign the profile to a particular user:

SQL> alter user jh profile really_small;

User altered.

Just for kicks, you can check that your profile is assigned to your user.

SQL> select username, profile from dba_users where username = 'JH';

USERNAME     PROFILE
------------ ---------------
JH           REALLY_SMALL

SQL> select resource_name, resource_type, limit
2  from dba_profiles
3  where profile = 'REALLY_SMALL';

RESOURCE_NAME                    RESOURCE LIMIT
-------------------------------- -------- ------------------
COMPOSITE_LIMIT                  KERNEL   DEFAULT
SESSIONS_PER_USER                KERNEL   1
CPU_PER_SESSION                  KERNEL   100
CPU_PER_CALL                     KERNEL   100
LOGICAL_READS_PER_SESSION        KERNEL   DEFAULT
LOGICAL_READS_PER_CALL           KERNEL   DEFAULT
IDLE_TIME                        KERNEL   DEFAULT
CONNECT_TIME                     KERNEL   5
PRIVATE_SGA                      KERNEL   DEFAULT
FAILED_LOGIN_ATTEMPTS            PASSWORD DEFAULT
PASSWORD_LIFE_TIME               PASSWORD DEFAULT
PASSWORD_REUSE_TIME              PASSWORD DEFAULT
PASSWORD_REUSE_MAX               PASSWORD DEFAULT
PASSWORD_VERIFY_FUNCTION         PASSWORD DEFAULT
PASSWORD_LOCK_TIME               PASSWORD DEFAULT
PASSWORD_GRACE_TIME              PASSWORD DEFAULT

16 rows selected.

Your user connects to the database, starts running his monster query, and is promptly disconnected:

$ sqlplus jh/jh@mydb

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Apr 4 20:34:23 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production

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

Sweet.