Profiles

A profile is a collection of attributes that apply to a user.
The profile is used to enable a single point of reference for multiple users who share these
attributes.
You should assign a profile to each user. Each user can have only one profile.

Profile resource limits are enforced only when you enable resource limitation for the associated
database. Enabling this limitation can occur either before starting the database (using the
RESOURCE_LIMIT initialization parameter) or while it is open (using the ALTER SYSTEM statement)

Prerequisites

To create a profile, you must have the CREATE PROFILE system privilege.

To specify resource limits for a user, you must:

• Enable resource limits dynamically with the ALTER SYSTEM statement or with the
initialization parameter RESOURCE_LIMIT. This parameter does not apply to password
resources. Password resources are always enabled.

• Create a profile that defines the limits using the CREATE PROFILE statement

• Assign the profile to the user using the CREATE USER or ALTER USER statement

To find the default profile values, you can run the following query:

col PROFILE for a20
col RESOURCE for a20
col LIMIT for a30
set lin 200 pages 200
SELECT * FROM DBA_PROFILES WHERE PROFILE = 'DEFAULT';

PROFILE 	     RESOURCE_NAME		      RESOURCE LIMIT			      COM INH IMP
-------------------- -------------------------------- -------- ------------------------------ --- --- ---
DEFAULT 	     COMPOSITE_LIMIT		      KERNEL   UNLIMITED		      NO  NO  NO
DEFAULT 	     SESSIONS_PER_USER		      KERNEL   UNLIMITED		      NO  NO  NO
DEFAULT 	     CPU_PER_SESSION		      KERNEL   UNLIMITED		      NO  NO  NO
DEFAULT 	     CPU_PER_CALL		      KERNEL   UNLIMITED		      NO  NO  NO
DEFAULT 	     LOGICAL_READS_PER_SESSION	      KERNEL   UNLIMITED		      NO  NO  NO
DEFAULT 	     LOGICAL_READS_PER_CALL	      KERNEL   UNLIMITED		      NO  NO  NO
DEFAULT 	     IDLE_TIME			      KERNEL   UNLIMITED		      NO  NO  NO
DEFAULT 	     CONNECT_TIME		      KERNEL   UNLIMITED		      NO  NO  NO
DEFAULT 	     PRIVATE_SGA		      KERNEL   UNLIMITED		      NO  NO  NO
DEFAULT 	     FAILED_LOGIN_ATTEMPTS	      PASSWORD 10			      NO  NO  NO
DEFAULT 	     PASSWORD_LIFE_TIME 	      PASSWORD 180			      NO  NO  NO
DEFAULT 	     PASSWORD_REUSE_TIME	      PASSWORD UNLIMITED		      NO  NO  NO
DEFAULT 	     PASSWORD_REUSE_MAX 	      PASSWORD UNLIMITED		      NO  NO  NO
DEFAULT 	     PASSWORD_VERIFY_FUNCTION	      PASSWORD NULL			      NO  NO  NO
DEFAULT 	     PASSWORD_LOCK_TIME 	      PASSWORD 1			      NO  NO  NO
DEFAULT 	     PASSWORD_GRACE_TIME	      PASSWORD 7			      NO  NO  NO
DEFAULT 	     INACTIVE_ACCOUNT_TIME	      PASSWORD UNLIMITED		      NO  NO  NO
DEFAULT 	     PASSWORD_ROLLOVER_TIME	      PASSWORD -1			      NO  NO  NO

18 rows selected.

Creating a Profile

• A profile can encompass limits for a specific category, such as limits on passwords or limits
 on resources.
• To create a profile, you must have the CREATE PROFILE system privilege.
• To find all existing profiles, you can query the DBA_PROFILES view.

Note: password lock time by default is for 1 day. You can specify it in minutes (n/1440) or even in seconds (n/86400).

For example, to create a profile that defines password limits:

CREATE PROFILE password_profile
LIMIT
FAILED_LOGIN_ATTEMPTS 2
PASSWORD_LIFE_TIME 60 PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX 5
PASSWORD_LOCK_TIME 1/24
PASSWORD_GRACE_TIME 10
PASSWORD_VERIFY_FUNCTION DEFAULT;

The following example shows how to create a resource limits profile.

CREATE PROFILE user_profile
LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3500
CONNECT_TIME 50
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1200
PRIVATE_SGA 20K
COMPOSITE_LIMIT 7000000;

If you assign the user_profile 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 35 seconds of CPU time.

• A single session cannot last for more than 45 minutes.

• In a single session, the number of data blocks read 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 1200 data blocks from memory and disk.

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

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

• Since the user profile omits a limit for IDLE_TIME and for password limits, the user is subject to the limits on these resources specified in the DEFAULT profile.

Assigning a Profile to a User

After you create a profile, you can assign it to users.

You can assign a profile to a user who has already been assigned a profile, but the most recently assigned profile takes precedence. When you assign a profile to an external user or a global user, the password parameters do not take effect for that user.

To find the profiles that are currently assigned to users, you can query the DBA_USERS view.

• Use the ALTER USER statement to assign the profile to a user.

For example:
ALTER USER usr2 PROFILE password_prof

Dropping Profiles

You can drop a profile, even if it is currently assigned to a user.

When you drop a profile, the drop does not affect currently active sessions. Only sessions that were created after a profile is dropped use the modified profile assignments. To drop a profile, you must have the DROP PROFILE system privilege.

NOTE:
You cannot drop the default profile.

Any user currently assigned to a profile that is dropped is automatically is assigned to the DEFAULT profile. The DEFAULT profile cannot be dropped.

• Use the SQL statement DROP PROFILE to drop a profile. To drop a profile that is currently assigned to a user, use the CASCADE option.

For example:
DROP PROFILE clerk CASCADE;

Query to View Memory Use for Each User Session

The V$SESSION dynamic view lists the memory use for each user session.

Note: Resource management parameters will take in effect only if RESOURCE_LIMIT parameter is set to TRUE.


Use below command to check the RESOURCE_LIMIT parameter
SQL> show parameter resource_limit;

By default the parameter is set to FALSE. You can change it via below
SQL> alter system set resource_limit=TRUE scope=both;

To check profiles assigned to a user
SQL> SELECT USERNAME, PROFILE FROM DBA_USERS WHERE USERNAME='USR1';

To check profile parameter values
SQL> SELECT * FROM DBA_PROFILES WHERE PROFILE='&PROFILE_NAME';

The following query lists all current sessions, showing the Oracle Database user and current User Global Area (UGA) memory use for each session:

SELECT USERNAME, VALUE || 'bytes' "Current UGA memory" FROM V$SESSION sess, V$SESSTAT stat,V$STATNAME name WHERE sess.SID = stat.SID AND stat.STATISTIC# = name.STATISTIC# AND name.NAME = 'session uga memory';