Master Oracle Profiles: Limits, Parameters, and Example Creation

1. About Oracle Profiles

Profiles in Oracle are collections of password and resource limits that control CPU time, I/O usage, idle time, connection time, concurrent sessions, password policies, etc. They can be applied at session or statement level. Oracle ships with many default profiles that are often unlimited, and new users are assigned a profile.

2. Profile Parameters Explained

Resource parameters (RESOURCE_TYPE=KERNEL):

SESSIONS_PER_USER : maximum concurrent sessions per user.

CPU_PER_SESSION : CPU time limit per session, in hundredths of a second.

CPU_PER_CALL : CPU time limit per call (parse, execute, fetch), in hundredths of a second.

CONNECT_TIME : total connection time per session, in minutes.

IDLE_TIME : allowed continuous inactivity per session, in minutes; exceeding this disconnects the session.

LOGICAL_READS_PER_SESSION : maximum number of data blocks read by a session (memory or disk).

LOGICAL_READS_PER_CALL : maximum data blocks read per SQL execution call.

PRIVATE_SGA : maximum space a session may allocate in the shared pool (bytes).

COMPOSITE_LIMIT : total resource consumption per session expressed in service units.

Password parameters (RESOURCE_TYPE=PASSWORD):

FAILED_LOGIN_ATTEMPTS : maximum login failures before the account is locked.

PASSWORD_LIFE_TIME : number of days a password may be used.

PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX : together control how many days and how many password changes must occur before a password can be reused; both must be integers.

PASSWORD_LOCK_TIME : lock duration after reaching the failed‑login limit, in days.

PASSWORD_GRACE_TIME : grace period (in days) after a password expires before it becomes invalid.

PASSWORD_VERIFY_FUNCTION : name of a PL/SQL function that validates passwords; NULL disables verification.

3. Example: Creating a Custom Profile

The following SQL creates a profile named my_profile with specific limits:

CREATE PROFILE my_profile LIMIT
  SESSIONS_PER_USER UNLIMITED
  CPU_PER_SESSION UNLIMITED
  CPU_PER_CALL 6000
  CONNECT_TIME 60
  LOGICAL_READS_PER_SESSION DEFAULT
  LOGICAL_READS_PER_CALL 6000
  COMPOSITE_LIMIT 6000000
  PRIVATE_SGA 66K
  FAILED_LOGIN_ATTEMPTS 6
  PASSWORD_LIFE_TIME 60
  PASSWORD_REUSE_TIME 60
  PASSWORD_REUSE_MAX 5
  PASSWORD_LOCK_TIME 1/24
  PASSWORD_GRACE_TIME 10;

This profile can be assigned to a user when the user is created.