What is an Oracle Profile

It is a set of limits on database resources. If you assign the profile to a user, then that user cannot exceed these limits. To create a profile, you must have the CREATE PROFILE system privilege. A user with no profile assigned receives, by default, a profile named DEFAULT

It is based on two parameters Resource_parameters and Password_parameters


Resource parameters:

SESSIONS_PER_USER 

SESSIONS_PER_USER is used to determine the maximum number of sessions (connections to the database) a user can have simultaneously. If a user has reached the limit set in the SESSIONS_PER_USER resource of his or her profile, then the next login (and any subsequent ones) produce an error condition. Error ORA-02391 will occur when it is validated.

CPU_PER_SESSION 

Each query a user issues consumes an amount of CPU time, which varies dependent upon the query. By setting this resource item, the DBA limits the amount of CPU time a user can consume from a single database session. After reaching the CPU limit, the user can perform no further activity in that session. The user must disconnect from the database and then reconnect to reset this CPU accumulator. 
 
It is expressed in hundredth of seconds.

Alter profile app_user limit CPU_PER_SESSION 100;
Means the user consumes 1 second of CPU time for any query.
 
CPU_PER_CALL

This resource, like CPU_PER_SESSION, represents the total amount of CPU time (in minutes) available to the user. However, this resource restricts the user on a per-call (SQL statement) basis rather than a per-session basis. Whenever a SQL statement reaches its limit, it ends with an error condition. Unlike CPU_PER_SESSION, however, the user has no need to disconnect from the database. When using CPU_PER_CALL, the user is free to issue another query as long as it does not exceed the total amount of time specified in CPU_PER_CALL. Error ORA-02392 error will occur when it is validated.
 
CONNECT_TIME

Specify the total elapsed time limit for a session, expressed in minutes. Error ORA-02399 will occur when it is validated.
 
IDLE_TIME


Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit. Error ORA-02396 error will occur when it is validated.
  
LOGICAL_READS_PER_SESSION

What CPU_PER_SESSION is to LOGICAL_READS_PER_SESSION, CPU_PER_CALL is to LOGICAL_READS_PER_CALL. The value of this parameter restricts the number of database blocks that can be read during a single CPU call (SQL statement). If the number of blocks that the database attempts to logically read exceeds the limit set, the operation is abandoned. The user may issue another SQL statement and have no problems unless the logical reads in this statement exceed the value. Error ORA-02394 error will occur when it is validated.
 
LOGICAL_READS_PER_CALL
 
What CPU_PER_SESSION is to LOGICAL_READS_PER_SESSION, CPU_PER_CALL is to LOGICAL_READS_PER_CALL. The value of this parameter restricts the number of database blocks that can be read during a single CPU call (SQL statement). If the number of blocks that the database attempts to logically read exceeds the limit set, the operation is abandoned. The user may issue another SQL statement and have no problems unless the logical reads in this statement exceed the value. Error   ORA-02395 will occur when it is validated
 
PRIVATE_SGA_PER_SESSION 
 
Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA).     |
 
COMPOSITE_LIMIT
 
Specify the total resource cost for a session, expressed in service units.Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION,CONNECT_TIME, LOGICAL_READS_PER_SESSION,
and PRIVATE_SGA.

Password_parameters

FAILED_LOGIN_ATTEMPTS:

Specify the number of failed attempts to log in to the user
account before the account is locked.

PASSWORD_LIFE_TIME:

Specify the number of days the same password can be used forauthentication. If you also set a value for PASSWORD_GRACE_TIME,the password expires if it is not changed within the grace period, and furtherconnections are rejected. If you do notset a value for PASSWORD_GRACE_TIME,its default of UNLIMITED willcause the database to issue a warning but let the user continue to connect
indefinitely.

PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX:

PASSWORD_REUSE_TIME specifies the number of days before which apassword cannot be reused. PASSWORD_REUSE_MAX specifies the number of password
changes required before the current password can be reused.
For example, if you specifyPASSWORD_REUSE_TIME to 30 and PASSWORD_REUSE_MAX to 10, then the user can reuse
the password after 30 days if the password has already been changed 10 times.

PASSWORD_LOCK_TIME 
Specify the number of days an account will be locked after the
specified number of consecutive failed login attempts.


PASSWORD_VERIFY_FUNCTION:
The PASSWORD_VERIFY_FUNCTION clauselets a PL/SQL password complexity verification script be passed as an argument
to the CREATE PROFILE statement

After setting all above parameters you are now ready to create the
profile:

First set the resource_limit parameter to true.

CREATE PROFILE pro
  LIMIT PASSWORD_REUSE_MAX 10
        PASSWORD_REUSE_TIME 30;

CREATE PROFILE app_user LIMIT 
   SESSIONS_PER_USER          UNLIMITED 
   CPU_PER_SESSION            UNLIMITED 
   CPU_PER_CALL               3000 
   CONNECT_TIME               45 
   LOGICAL_READS_PER_SESSION  DEFAULT 
   LOGICAL_READS_PER_CALL     1000 
   PRIVATE_SGA                15K
   COMPOSITE_LIMIT            5000000; 


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 30 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 1000 data blocks from memory and disk.


A single session cannot allocate more than 15 kilobytes of memory in the SGA.
In a single session, the total resourcecost cannot exceed 5 million service units. The formula for calculating the total resource cost is specified by the ALTERRESOURCE COST statement.


Since the app_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
 
Example:
 
SQL>ALTER SYSTEM SET RESOURCE_LIMIT = true SCOPE=MEMORY;
 
System altered.
 
SQL> CREATE PROFILE save_cpu_time LIMIT cpu_per_session 1;
 
Profile created.
 
SQL>ALTER USER SCOTT PROFILE save_cpu_time;
 
User altered.
 
SQL> CONNECT scott/tiger
Connected.
 
SQL>CREATE TABLE bigemp AS SELECT * FROM emp;
 
Table created.
 
SQL> INSERT INTO bigemp SELECT * FROM bigemp;
INSERT INTO bigemp SELECT * FROM bigemp
            *
ERROR at line 1:
ORA-02392: exceeded session limit on CPU usage, you are being logged off
 
ENJOY:-)