Oracle Database Resource Management and Profiles

Resources and Consumer Groups

See Resource Management and Consumer Groups.

Profiles

Profiles allow the regulation and limiting of resources by assigning limitations to different users through the use of profiles. All users are given the DEFAULT profile on creation which allows unlimited use of all resources.

CREATE PROFILE profile LIMIT
{
	{
		SESSION_PER_USER
		CPU_PER_SESSION
		CPU_PER_CALL
		CONNECT_TIME
		IDLE_TIME
		LOGICAL_READS_PER_SESSION
		LOGICAL_READS_PER_CALL
		COMPOSITE_LIMIT
	} { n | UNLIMITED | DEFAULT }

	PRIVATE_SGA {n [K|M] | UNLIMITED | DEFAULT}
} [ ... ]
;

For example:

CREATE PROFILE developer LIMIT
	IDLE_TIME 1
	FAILED_LOGIN_ATTEMPTS 3
	PASSWORD_LOCK_TIME 1/24
	PASSWORD_LIFE_TIME 90
	PASSWORD_GRACE_TIME 3;

In the example below the user jim will be allocated 36 seconds of active time (CPU cycles are measured in 100's of a second) and 30 minutes of idle time (idle time is measured in minutes).

ALTER PROFILE jim LIMIT CPU_PER_SESSION 3600 IDLE_TIME 30;

ALTER PROFILE profile LIMIT
{
	{
		SESSION_PER_USER
		CPU_PER_SESSION
		CPU_PER_CALL
		CONNECT_TIME
		IDLE_TIME
		LOGICAL_READS_PER_SESSION
		LOGICAL_READS_PER_CALL
		COMPOSITE_LIMIT
	} { n | UNLIMITED | DEFAULT }

	PRIVATE_SGA {n [K|M] | UNLIMITED | DEFAULT}
} [ ... ]
;

Below is a query of the DBA_PROFILES view showing the possible profile settings.

SELECT profile||', '||resource_name||', '||resource_type||', '||limit FROM dba_profiles ORDER BY profile, resource_name;

PROFILE RESOURCE_NAME          		RESOURCE_TYPE	LIMIT
------- ------------------------------- --------------- ---------
DEFAULT COMPOSITE_LIMIT			KERNEL	 	UNLIMITED
DEFAULT CONNECT_TIME			KERNEL	 	UNLIMITED
DEFAULT CPU_PER_CALL			KERNEL	 	UNLIMITED
DEFAULT CPU_PER_SESSION			KERNEL	 	UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS		PASSWORD 	UNLIMITED
DEFAULT IDLE_TIME			KERNEL	 	UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL		KERNEL	 	UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION	KERNEL	 	UNLIMITED
DEFAULT PASSWORD_GRACE_TIME		PASSWORD 	UNLIMITED
DEFAULT PASSWORD_LIFE_TIME		PASSWORD 	UNLIMITED
DEFAULT PASSWORD_LOCK_TIME		PASSWORD 	UNLIMITED
DEFAULT PASSWORD_REUSE_MAX		PASSWORD 	UNLIMITED
DEFAULT PASSWORD_REUSE_TIME		PASSWORD 	UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION	PASSWORD 	UNLIMITED
DEFAULT PRIVATE_SGA			KERNEL	 	UNLIMITED
DEFAULT SESSIONS_PER_USER		KERNEL	 	UNLIMITED

Setting Profile Resource

Setting of resource limits on profiles assigned to users prevents users from for instance, locking up the database with erroneous adhoc SQL queries or leaving a connection connected to the database. Resource limits can effectively used to enforce security to a certain extent. Resource limits can be applied at session and call level or both. Session level implies that the currently open transaction is completed and no further transactions are allowed until a commit or rollback is issued. Note that resource limits set for a specific user supercede resource costs assigned to a specific profile.

ALTER RESOURCE COST
{
	{
		
		CPU_PER_SESSION
		CONNECT_TIME
		LOGICAL_READS_PER_SESSION
		PRIVATE_SGA
	}
} [ ... ]
;