6.3. Controlling Resource Usage by Users

An Oracle 10g database lets you limit some resources that your user accounts consume. Diskspace limits are governed by tablespace quotas, and CPU, memory, and hybrid limits are implemented with profiles. In the following sections, you will learn how to manage resource consumption in your database with roles and quotas.

6.3.1. Assigning Tablespace Quotas

Before a user can create objects in a tablespace, they must have a space usage quota for that tablespace. Tablespace quotas limit the amount of disk space that a user can consume. The default quota is none, which is why you need to assign a quota before you can create objects in a tablespace. You can assign a space usage quota at the same time you create a user, with the CREATE USER statement:

CREATE USER chip IDENTIFIED BY "Seek!r3t"
QUOTA 100M ON USERS;

Or after the user has been created with the ALTER USER statement:

ALTER USER bart
QUOTA UNLIMTED ON USERS;

The special keyword UNLIMITED tells the database that the user should not have a preset limit on the amount of space that their objects can consume.

6.3.2. Assigning Resource Limits with a Profile

CPU and session-oriented resource limits are managed through the use of a profile. Profiles let you set limits for several resources, including CPU time, memory, and the number of logical reads performed during a user session or database call. A database call is either a parse, an execute, or a fetch. Usually, the database implicitly performs these calls for you. You can explicitly make these database calls from Java, PL/SQL, or OCI (Oracle Call Interface) programs.

A logical read is a measure of the amount of work that the database performs while executing SQL statements. Statements that generate more logical reads require the database to perform more work than statements generating fewer logical reads. Technically, a logical read is counted for each row accessed via ROWID (index access) and for each data block accessed via multiblock read (full table scan or index fast full scan).

To enable resource limit restrictions with profiles, first enable them in the database by setting the initialization parameter resource_limit to true, like this:

ALTER SYSTEM SET resource_limit = TRUE SCOPE = BOTH;

To assign resource limits to a profile, use the CREATE PROFILE or ALTER PROFILE statement with one or more of the kernel resource parameters. These statements support the following clauses to limit resources:

CONNECT_TIME Limits any session established by a user having this profile to the specified number of minutes. Connection time is sometimes called "wall clock time" to differentiate it from CPU time. When a session exceeds the specified number of minutes, the database rolls back any uncommitted changes and terminates the session. The next call to the database raises an exception. You can use the special value UNLIMITED to tell the database that there is no limit to a session's duration. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this:

CREATE PROFILE agent LIMIT CONNECT_TIME 10;

ALTER PROFILE data_analyst LIMIT CONNECT_TIME UNLIMITED;

CPU_PER_CALL Limits the amount of CPU time that can be consumed by any single database call in any session established by a user with this profile. The specified value is in hundredths of a second and applies to a parse, an execute, or a fetch call. These calls are implicitly performed by the database for any SQL statement executed in SQL*Plus and can be explicitly called from OCI, Java, or PL/SQL programs. When this limit is breached, the statement fails and is automatically rolled back, and an exception is raised. The user can then commit or roll back any uncommitted changes in the transaction. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this:

CREATE PROFILE agent LIMIT CPU_PER_CALL 3000;

ALTER PROFILE data_analyst LIMIT CPU_PER_CALL UNLIMITED;

CPU_PER_SESSION Limits the amount of CPU time that can be consumed in any session established by a user with this profile. The specified value is in hundredths of a second. When this limit is breached, the current statement fails, the transaction is automatically rolled back, and an exception is raised. The user can then commit or roll back any uncommitted changes in the transaction before logging off. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this:

CREATE PROFILE agent LIMIT CPU_PER_CALL 30000;
ALTER PROFILE data_analyst LIMIT CPU_PER_CALL UNLIMITED;

IDLE_TIME Limits the duration of time between database calls to the specified number of minutes. If a user having this profile exceeds this setting, the next statement fails, and the user is allowed to either commit or roll back any uncommitted changes before logging off. Long-running statements are not affected by this setting. Set IDLE_TIME in a CREATE PROFILE or ALTER PROFILE statement like this:

CREATE PROFILE agent LIMIT IDLE_TIME 10;

ALTER PROFILE daemon LIMIT IDLE_TIME UNLIMITED;

LOGICAL_READS_PER_CALL Caps the amount of work that any individual database call performs to the specified number of logical reads. The database call is either a parse, an execute, or a fetch. If the limit is exceeded, the database rolls back the statement, returns an error to the calling program, and allows the user to either commit or roll back any uncommitted changes. Logical reads are computed as the sum of consistent gets plus current mode gets. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this:

CREATE PROFILE agent LIMIT LOGICAL_READS_PER_CALL 2500;

ALTER PROFILE data_analyst LIMIT LOGICAL_READS_PER_CALL 1000000;

LOGICAL_READS_PER_SESSION Limits the amount of database work that a user's session can consume to the specified number of logical reads. When the limit is exceeded, the current statement fails and an exception is raised, and the user must either commit or roll back the transaction and end the session. Logical reads are computed as the sum of consistent gets plus current mode gets. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this:

CREATE PROFILE agent LIMIT LOGICAL_READS_PER_SESSION 250000;

ALTER PROFILE data_analyst
   LIMIT LOGICAL_READS_PER_SESSION 35000000;

PRIVATE_SGA Limits the amount of SGA (System Global Area) memory in bytes that a user connecting with shared servers (via multithreaded server [MTS]) can allocate to the persistent area in the PGA (Program Global Area). This area contains bind information among other items. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this:

CREATE PROFILE agent LIMIT PRIVATE_SGA 2500;

ALTER PROFILE data_analyst LIMIT PRIVATE_SGA UNLIMITED;

SESSIONS_PER_USER Restricts a user with this profile to the specified number of database sessions. This setting can be useful to discourage DBAs from all connecting to a shared administrative account to do their work when corporate policy indicates that they should be connecting to their individual accounts. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this:

CREATE PROFILE admin_profile LIMIT SESSIONS_PER_USER 2;

ALTER PROFILE data_analyst LIMIT SESSIONS_PER_USER 6;

COMPOSITE_LIMIT Limits the number of service units that can be consumed during a user session. Service units are calculated as the weighted sum of CPU_PER_SESSION, LOGICAL_ READS_PER_SESSION, CONNECT_TIME, and PRIVATE_SGA values. The weightings are established with the ALTER RESOURCE COST statement and can be viewed from the RESOURCE_COST data dictionary view. This COMPOSITE_LIMIT allows you to cap the resource consumption of user groups in more complex ways than a single resource limit. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this:

CREATE PROFILE admi_profile LIMIT COMPOSITE_LIMIT UNLIMITED;

ALTER PROFILE data_analyst LIMIT COMPOSITE_LIMIT 100000;

To enforce the resource limits established with profiles, you must enable them by setting the initialization parameter RESOURCE_LIMIT to TRUE. The default setting is FALSE. Set this parameter with the ALTER SYSTEM statement, like this:

ALTER SYSTEM SET resource_limit = TRUE SCOPE=BOTH;

You can also use profiles to manage passwords.