Oracle7 Server Administrator's Guide

Contents Index Home Previous Next

Managing Resources with Profiles

A profile is a named set of resource limits. If resource limits are turned on, Oracle limits database usage and instance resources to whatever is defined in the user's profile. You can assign a profile to each user, and a default profile to all users who do not have specific profiles. For profiles to take effect, resource limits must be turned on for the database as a whole.

This section describes aspects of profile management, and includes the following topics:

Creating Profiles

To create a profile, you must have the CREATE PROFILE system privilege. You can create profiles using either the Create Profile property sheet of Server Manager/GUI, or the SQL command CREATE PROFILE. At the same time, you can explicitly set particular resource limits.

The following statement creates the profile CLERK:

CREATE PROFILE clerk LIMIT
   SESSIONS_PER_USER 2
   CPU_PER_SESSION unlimited
   CPU_PER_CALL 6000
   LOGICAL_READS_PER_SESSION unlimited
   LOGICAL_READS_PER_CALL 100
   IDLE_TIME 30
   CONNECT_TIME 480;

All unspecified resource limits for a new profile take the limit set by the DEFAULT profile. You can also specify limits for the DEFAULT profile.

Using the DEFAULT Profile

Each database has a DEFAULT profile, and its limits are used in two cases:

Initially, all limits of the DEFAULT profile are set to UNLIMITED. However, to prevent unlimited resource consumption by users of the DEFAULT profile, the security administrator should change the default limits using the Alter Profile dialog box of Server Manager, or a typical ALTER PROFILE statement:

ALTER PROFILE default LIMIT
   . . . ;

Any user with the ALTER PROFILE system privilege can adjust the limits in the DEFAULT profile. The DEFAULT profile cannot be dropped.

Assigning Profiles

After a profile has been created, you can assign it to database users. Each user can be assigned only one profile at any given time. If a profile is assigned to a user who already has a profile, the new profile assignment overrides the previously assigned profile. Profile assignments do not affect current sessions. Profiles can be assigned only to users and not to roles or other profiles.

Profiles can be assigned to users using the Assign Profile dialog box of Server Manager/GUI, or the SQL commands CREATE USER or ALTER USER.

See Also: For more information about assigning a profile to a user, see [*] and [*].

Altering Profiles

You can alter the resource limit settings of any profile using either the Alter Profile property sheet of Server Manager/GUI, or the SQL command ALTER PROFILE. To alter a profile, you must have the ALTER PROFILE system privilege.

Any adjusted profile limit overrides the previous setting for that profile limit. By adjusting a limit with a value of DEFAULT, the resource limit reverts to the default limit set for the database. All profiles not adjusted when altering a profile retain the previous settings. Any changes to a profile do not affect current sessions. New profile settings are used only for sessions created after a profile is modified.

The following statement alters the CLERK profile:

ALTER PROFILE clerk LIMIT
   CPU_PER_CALL default
   LOGICAL_READS_PER_SESSION 20000;

See Also: For information about default profiles, see "Using the Default Profile" [*].

Using Composite Limits

You can limit the total resource cost for a session via composite limits. In addition to setting specific resource limits explicitly for a profile, you can set a single composite limit that accounts for all resource limits in a profile. You can set a profile's composite limit using the Composite Limit checkbox of the Create Profile and Alter Profile property sheets of Server Manager/GUI, or the COMPOSITE_LIMIT parameter of the SQL commands CREATE PROFILE or ALTER PROFILE. A composite limit is set via a service unit, which is a weighted sum of all resources used.

The following CREATE PROFILE statement is defined using the COMPOSITE_LIMIT parameter:

CREATE PROFILE clerk LIMIT
   COMPOSITE_LIMIT 20000
   SESSIONS_PER_USER 2
   CPU_PER_CALL 1000;

Notice that both explicit resource limits and a composite limit can exist concurrently for a profile. The limit that is reached first stops the activity in a session. Composite limits allow additional flexibility when limiting the use of system resources.

Determining the Value of the Composite Limit

The correct service unit setting for a composite limit depends on the total amount of resource used by an average profile user. As with each specific resource limit, historical information should be gathered to determine the normal range of composite resource usage for a typical profile user.

Setting Resource Costs

Each system has its own characteristics; some system resources may be more valuable than others. Oracle enables you to give each system resource a cost. Costs weight each system resource at the database level. Costs are only applied to the composite limit of a profile; costs do not apply to set individual resource limits explicitly.

To set resource costs, you must have the ALTER RESOURCE system privilege.

Only certain resources can be given a cost, including CPU_PER_- SESSION, LOGICAL_READS_PER_SESSION, CONNECT_TIME, and PRIVATE_SGA. Set costs for a database using the SQL command ALTER RESOURCE COST:

ALTER RESOURCE COST
   CPU_PER_SESSION 1
   LOGICAL_READS_PER_SESSION 50;

A large cost means that the resource is very expensive, while a small cost means that the resource is not expensive. By default, each resource is initially given a cost of 0. A cost of 0 means that the resource should not be considered in the composite limit (that is, it does not cost anything to use this resource). No resource can be given a cost of NULL.

See Also: For additional information and recommendations on setting resource costs, see your operating system-specific Oracle documentation.

Dropping Profiles

To drop a profile, you must have the DROP PROFILE system privilege. You can drop a profile using either Server Manager/GUI, or the SQL command DROP PROFILE. To successfully drop a profile currently assigned to a user, use the CASCADE option.

The following statement drops the profile CLERK, even though it is assigned to a user:

DROP PROFILE clerk CASCADE;

Any user currently assigned to a profile that is dropped is automatically assigned to the DEFAULT profile. The DEFAULT profile cannot be dropped. Note that when a profile is dropped, the drop does not affect currently active sessions; only sessions created after a profile is dropped abide by any modified profile assignments.

Enabling and Disabling Resource Limits

A profile can be created, assigned to users, altered, and dropped at any time by any authorized database user, but the resource limits set for a profile are enforced only when you enable resource limitation for the associated database. Resource limitation enforcement can be enabled or disabled by two different methods, as described in the next two sections.

To alter the enforcement of resource limitation while the database remains open, you must have the ALTER SYSTEM system privilege.

Enabling and Disabling Resource Limits Before Startup

If a database can be temporarily shut down, resource limitation can be enabled or disabled by the RESOURCE_LIMIT initialization parameter in the database's parameter file. Valid values for the parameter are TRUE (enables enforcement) and FALSE; by default, this parameter's value is set to FALSE. Once the parameter file has been edited, the database instance must be restarted to take effect. Every time an instance is started, the new parameter value enables or disables the enforcement of resource limitation.

Enabling and Disabling Resource Limits While the Database is Open

If a database cannot be temporarily shut down or the resource limitation feature must be altered temporarily, you can enable or disable the enforcement of resource limitation using the SQL command ALTER SYSTEM. After an instance is started, an ALTER SYSTEM statement overrides the value set by the RESOURCE_LIMIT parameter. For example, the following statement enables the enforcement of resource limitation for a database:

ALTER SYSTEM
   SET RESOURCE_LIMIT = TRUE;

An ALTER SYSTEM statement does not permanently determine the enforcement of resource limitation. If the database is shut down and restarted, the enforcement of resource limits is determined by the value set for the RESOURCE_LIMIT parameter.

Contents Index Home Previous Next