Oracle7 Server SQL Reference

CREATE PROFILE

Purpose

To create a profile. A profile is a set of limits on database resources. If you assign the profile to a user, that user cannot exceed these limits.

Prerequisites

You must have CREATE PROFILE system privilege.

Syntax

Keywords and Parameters

profile

is the name of the profile to be created.

SESSIONS_PER_USER

limits a user to

integer concurrent sessions.

CPU_PER_SESSION

limits the CPU time for a session. This value is expressed in hundredths of seconds.

CPU_PER_CALL

limits the CPU time for a call (a parse, execute, or fetch). This value is expressed in hundredths of seconds.

CONNECT_TIME

limits the total elapsed time of a session. This value is expressed in minutes.

IDLE_TIME

limits periods of continuous inactive time during a session. This value is expressed in minutes. Long-running queries and other operations are not subject to this limit.

LOGICAL_READS_PER_SESSION

limits the number of data blocks read in a session, including blocks read from memory and disk, to

integer blocks.

LOGICAL_READS_PER_CALL

limits the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch) to

integer blocks.

PRIVATE_SGA

limits the amount of private space a session can allocate in the shared pool of the System Global Area (SGA) to integer bytes. You can also use the K or M to specify this limit in kilobytes or megabytes. This limit only applies if you are using the multi-threaded server architecture. The private space for a session in the SGA includes private SQL and PL/SQL areas, but not shared SQL and PL/SQL areas.

COMPOSITE_LIMIT

limits the total resource cost for a session. You must express the value of this parameter in service units.

Oracle7 calculates the total resource cost as a weighted sum of the following resources:

CPU_PER_SESSION

CONNECT_TIME

LOGICAL_READS_PER_SESSION

PRIVATE_SGA

For information on how to specify the weight for each session resource see the ALTER RESOURCE COST command .

UNLIMITED

indicates that a user assigned this profile can use an unlimited amount of this resource.

DEFAULT

omits a limit for this resource in this profile. A user assigned this profile is subject to the limit for this resource specified in the DEFAULT profile.

Usage Notes

In Trusted Oracle7, the new profile is automatically labeled with your DBMS label.

Using Profiles

A profile is a set of limits on database resources. You can use profiles to limit the database resources available to a user for a single call or a single session. Oracle7 enforces resource limits in the following ways:

If a user exceeds the CONNECT_TIME or IDLE_TIME session resource limit, Oracle7 rolls back the current transaction and ends the session. When the user process next issues a call to Oracle7, an error message is returned.

If a user attempts to perform an operation that exceeds the limit for other session resources, Oracle7 aborts the operation, rolls back the current statement, and immediately returns an error. The user can then commit or roll back the current transaction. The user must then end the session.

If a user attempts to perform an operation that exceeds the limit for a single call, Oracle7 aborts the operation, rolls back the current statement, and returns an error message, leaving the current transaction intact.

How to Limit Resources

To specify resource limits for a user, you must perform both of the following operations:

Enable resource limits: You can enable resource limits through one of the following ways:

You can enable resources limits with the initialization parameter RESOURCE_LIMIT.

You can enable resource limits dynamically with the ALTER SYSTEM command. See the ALTER SYSTEM command 4 - 76.

Specify resource limits: To specify a resource limit for a user, you must perform following steps:

1. Create a profile that defines the limits using the CREATE PROFILE command.

2. Assign the profile to the user using the CREATE USER or ALTER USER command. Note that you can specify resource limits for users regardless of whether resource limits are enabled. However, Oracle7 does not enforce these limits until you enable them.

The DEFAULT Profile

Oracle7 automatically creates a default profile named DEFAULT. This profile initially defines unlimited resources. You can change the limits defined in this profile with the ALTER PROFILE command.

Any user who is not explicitly assigned a profile is subject to the limits defined in the DEFAULT profile. Also, if the profile that is explicitly assigned to a user omits limits for some resources or specifies DEFAULT for some limits, the user is subject to the limits on those resources defined by the DEFAULT profile.

Example

The following statement creates the profile SYSTEM_MANAGER:

CREATE PROFILE system_manager 
	LIMIT	SESSIONS_PER_USER          UNLIMITED 
			CPU_PER_SESSION            UNLIMITED 
			CPU_PER_CALL               3000 
			CONNECT_TIME               45 
			LOGICAL_READS_PER_SESSION  DEFAULT 
			LOGICAL_READS_PER_CALL     1000 
			PRIVATE SGA                15K 
			COMPOSITE_LIMIT            5000000

If you then assign the SYSTEM_MANAGER profile to a user, the user is subject to the following limits in subsequent sessions:

The user can have any number of concurrent sessions.

In a single session, the user can consume an unlimited amount of CPU time.

A single call made by the user cannot consume more than 30 seconds of CPU time.

A single session cannot last for more than 45 minutes.

In a single session, the number of data blocks from memory and disk is subject to the limit specified in the DEFAULT profile.

A single call made by the user cannot read more than 1000 total data blocks from memory and disk.

A single session cannot allocate more than 15 kilobytes of memory in the SGA.

In a single session, the total resource cost cannot exceed 5 million service units. The formula for calculating the total resource cost is specified by the ALTER RESOURCE COST command.

Since the SYSTEM_MANAGER profile omits a limit for IDLE_TIME, the user is subject to the limit on this resource specified in the DEFAULT profile.