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:
- SESSIONS_PER_USER
- CPU_PER_SESSION
- CPU_PER_CALL
- LOGICAL_READS_PER_SESSION
- LOGICAL_READS_PER_CALL
- IDLE_TIME
- CONNECT_TIME
- PRIVATE_SGA_PER_SESSION
- COMPOSITE_LIMIT
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 DEFAULT profile.
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.
Altering Profiles
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/manager@orcl Alter profile boss limit sessions_per_user 3 composite_limit default cpu_per_call unlimited;
The resource 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.
Deleting Profiles
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/manager@orcl 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;