In this tutorial, you will learn how to create, alter, and delete profiles in Oracle.
The database profile is Oracle’s attempt to enable you to exercise some method of resource management on the database. A profile is a named set of resource limits.
In Oracle, profiles control the number of resources a user can have. A list of profile resources follows:
It is important to note that you don’t need to specify every resource in every profile. Any profile resource you do not specifically set has the value of
DEFAULT, which corresponds to the value of the
Creating Profiles in Oracle
Any user (not necessarily the DBA) with sufficient database privileges can create the profiles in Oracle. In the following example, the DBA creates a profile named boss:
Create profile boss limit idle_time 30 cpu_per_call 600 logical_reads_per_session unlimited composite_limit 7500;
This profile is restricted by 30 minutes of idle time, 600 minutes of CPU time per call, or an overall composite limit of 7,500.
LOGICAL_READS_PER_SESSION is set to
UNLIMITED. All other resource values are set to
DEFAULT. Any user you associate with this profile is bound by these constraints.
As with most SQL commands, the
ALTER command provides a variation on the
CREATE command with which to make changes. The profiles are no different, and you can change any resource item in a profile by using this command, as the following example shows:
sqlplus system/[email protected] Alter profile boss limit sessions_per_user 3 composite_limit default cpu_per_call unlimited;
SESSION_PER_USER, which was previously
DEFAULT, now is set to 3. Deciding to go only with implicit profiles, the DBA also sets
COMPOSITE_LIMIT back to
DEFAULT and gives the boss profile
UNLIMITED CPU per call. These changes become useful for all users who are assigned the boss profile.
As the roles of users evolve, you may need to remove profiles from the database. You can do this simply and effectively by issuing the
Drop profile command. The following is an example:
sqlplus system/[email protected] Drop profile boss;
At this time, the boss profile no longer is available for use. If the profile currently is assigned to an existing user, an error condition occurs. You can override the error by using the
CASCADE option, which assigns the
DEFAULT profile to the user. The following is an example:
Drop profile boss cascade;