User profiles.

Learn How to Create, Alter, and Delete Profiles in Oracle

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

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;

See also:

Reference: