PROFILE Management in ORACLE

PROFILE Management (resource files)
When you need to set resource limits, you must set the database system startup parameter RESOURCE_LIMIT. The default value of this parameter is FALSE.
Run the following command to start the current resource limit:
Alter system set RESOURCE_LIMIT = true;
If you want to enable it later, you must set it in init <SID>. ora.
RESOURCE_LIMIT = true

Syntax creation:
Create profile profile LIMIT
{Resource_parameters | password_parameters}
[Resource_parameters | password_parameters]...;

{SESSIONS_PER_USER | CPU_PER_SESSION | CPU_PER_CALL | CONNECT_TIME | IDLE_TIME
| LOGICAL_READS_PER_SESSION | LOGICAL_READS_PER_CALL | COMPOSITE_LIMIT}
{Integer | UNLIMITED | DEFAULT}
| PRIVATE_SGA {integer [K | M] | UNLIMITED | DEFAULT}
}

{FAILED_LOGIN_ATTEMPTS | PASSWORD_LIFE_TIME | PASSWORD_REUSE_TIME
| PASSWORD_REUSE_MAX | PASSWORD_LOCK_TIME | PASSWORD_GRACE_TIME}
{Expr | UNLIMITED | DEFAULT}
| PASSWORD_VERIFY_FUNCTION {function | NULL | DEFAULT}
}

Create profile new_profile
LIMIT PASSWORD_REUSE_MAX DEFAULT
PASSWORD_REUSE_TIME UNLIMITED;

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 15 K
COMPOSITE_LIMIT 5000000;

Create profile app_user2 LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 60
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION verify_function
Pass word_lock_time 1/24
PASSWORD_GRACE_TIME 10;

1. PROFILE management content:
1. CPU time
2. I/O usage
3. idle time (idle time)
4. CONNECT TIME)
5. Number of concurrent sessions
6. Password mechanism:

Ii. default profile:
1. All users will be specified with this PROFILE when they are created.
2. The content of the default profile is empty and unlimited.

Iii. PROFILE Division:
If a user attempts to perform an operation that exceeds the limit for other session resources,
Oracle aborts the operation, rolls back the current statement, and immediately returns an error.
The user can then commit or roll back the current transaction, and must then end the session.
That exceeds the limit (exceeds the limit) for other session resources (other resources beyond the range)
It is understood that connections control resources other than the resource range.

When a user attempts to operate resources that exceed the limit, Oracle terminates the current operation, cancels the processing in progress, and returns an error,
You can submit or roll back the current transaction and disconnect the session.

If a user attempts to operate a resource that exceeds the limit, Oracle will discard the operation, cancel the processing being executed, and return immediately
An error is returned. This user then submits or rolls back the current processing and must terminate this session.

If it is a call restriction, Oracle terminates the current operation, cancels the processing in progress, and returns an error to roll back the entire transaction.

1. CALL-level LIMITE:
The object is a statement:
When the statement resource usage overflows:
A. The statement is terminated.
B. Transaction rollback
C. SESSION connection persistence

2. SESSION-level LIMITE:
Object: the entire session Process
Overflow: Connection terminated

4. How to manage a PROFILE
1. CREATE PROFILE
2. assign to a user
3. Open the limit like a switch.

5. How to create a PROFILE:
1. command:
Create profile name
LIMIT
SESSION_PER_USER 2
CPU _ per_session 1000
IDLE_TIME 60 how long a user can idle the connection before the database is terminated (minutes)
CONNECT_TIME 480

Vi. Restricted parameters:
0. UNLIMITED cancels resource restrictions
DEFAULT: the default resource limit is used.

1. CONNECT_TIME: The maximum connection time of a connection SESSION (minutes)
When the user's session time exceeds the time specified by CONNECT_TIME, Oracle rolls back the current transaction and ends his session
IDLE_TIME: The maximum connection time of a connection session
When the user's idle time exceeds the time specified by IDLE_TIME, Oracle rolls back the current transaction and ends his session

2. SESSIONS_PER_USER: the total number of concurrent sessions allowed by a user. If the number of concurrent sessions exceeds, the system disables subsequent sessions of the user and returns an error:
ORA-02391 exceeded simultaneous SESSIONS_PER_USER limit

3. SESSION-level LIMITE:
CPU_PER_SESSION: defines the CPU usage time of a SESSION (1/100 seconds). When this limit is reached, you cannot perform any operations in this SESSION,
In this case, you must first disconnect and then connect.
CPU_PER_CALL: limits the total CPU time available (parse, execute, and fetch) during each SQL statement call. The unit is 1% seconds. When the limit is reached
The statement ends with an error. Unlike CPU_PER_SESSION, a new query can be performed if the CPU_PER_SESSION limit is not reached.

4. LOGICAL_READS_PER_SESSION: Maximum number of logical blocks that a session can read and write

5. CALL-level LIMITE
CPU_PER_CALL: CPU time used by each statement
LOGICAL_READS_PER_CALL: the number of allowed read database blocks during a single SQL call
6. PRIVATE_SGA: maximum size of SGA that a session can allocate, defined by K/M
COMPOSITE_LIMIT: resource cost limit for a session, CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA

7. assign to a user:
Create user name ......
PROFILE name
Alter user name PROFILE name

8. Open resource restrictions:
1. RESOURCE_LIMT: The resource file contains
2. alter system set RESOURCE_LIMIT = TRUE;
3. disabled by default

9. modify the content of PROFIE:
1. New Value of alter profile name parameter
2. The modification does not take effect for the current connection.

DROP a PROFILE
1. drop profile name
Delete a new PROFILE that has not been assigned to the user,
2. drop profile name CASCADE
3. Notes
A. Once the PROFILE is deleted, the user is automatically loaded with the DEFAULT PROFILE
B. no impact on the current connection
C. The default profile cannot be deleted.

10. Information Retrieval:
1. DBA_USERS:
User name, PROFILE
2. DBA_PROFILES:
PROFILE and values of various restriction Parameters
Restrictions for each user: PROFILE (keyword segment)

11. Restrictions on the password mechanism of PROFILE
1. Restricted content
A. restrict the number of consecutive logon failures and lock the user
B. Restrict password Lifecycle
C. restrict the use interval of passwords
2. Prerequisites for the restriction to take effect:
A. RESOURCE_LIMIT: = TRUE
B. ORACLE \ RDBMS \ ADMIN \ UTLPWDMG. SQL
3. How to Create a password mechanism:
Create profile name
SESSIONS_PER_USER
.....
Password_life_time 30
Failed_log_attempts 3
Password_reuse_time 3
4. Parameter meaning:
A. FAILED_LOGIN_ATTEMPTS: the user is locked when the number of consecutive logon failures reaches the value specified by this parameter;
After being unlocked by DBA (or PASSWORD_LOCK_TIME Day), you can continue to use
B. PASSWORD_LIFE_TIME: Password validity period (days). The default value is UNLIMITED.
C. PASSWORD_LOCK_TIME: Number of lock days for the account due to the FAILED_LOGIN_ATTEMPTS lock
D. PASSWORD_GRACE_TIME: Password modification grace period (days)
E. PASSWORD_REUSE_TIME: the number of days after the password is modified, and the default value is UNLIMITED.
F. PASSWORD_REUSE_MAX: the number of times the original password is modified after the password is modified.
G, PASSWORD_VERIFY_FUNCTION: password verification function

12. error messages and Solutions
ORA-02390 exceeded COMPOSITE_LIMIT, you are being logged off
Cause: The COMPOSITE_LIMIT for the profile is exceeded. that is, the weighted sum of the connection time, logical reads per session, CPU usage per session, and private SGA space used during the session exceeded the limit set by the COMPOSITE_LIMIT clause set in the user profile.
Action: If this happens often, ask the database administrator to raise the COMPOSITE_LIMIT of the user profile, or determine which resource is used the most and raise the limit on that resource.

ORA-02391 exceeded simultaneous SESSIONS_PER_USER limit
Cause: An attempt was made to exceed the maximum number of concurrent sessions allowed by the SESSIONS_PER_USER clause of the user profile.
Action: End one or more concurrent sessions or ask the database administrator to increase the SESSIONS_PER_USER limit of the user profile. for more information about SESSIONS_PER_USER and the database administrator's specific tasks of adjusting concurrent sessions, see the Oracle9i SQL Reference and the Oracle9i Database Administrator's Guide.

ORA-02392 exceeded session limit on CPU usage, you are being logged off
Cause: An attempt was made to exceed the maximum CPU usage allowed by the CPU_PER_SESSION clause of the user profile.
Action: If this happens often, ask the database administrator to increase the CPU_PER_SESSION limit of the user profile.

ORA-02393 exceeded call limit on CPU usage
Cause: An attempt was made to exceed the maximum CPU time for a call, a parse, execute, or fetch, allowed by the CPU_PER_CALL clause of the user profile.
Action: If this happens often, ask the database administrator to increase the CPU_PER_CALL limit of the user profile.

ORA-02394 exceeded session limit on IO usage, you are being logged off
Cause: An attempt was made to exceed the maximum I/O allowed by the LOGICAL_READS_PER_SESSION clause of the user profile.
Action: If this happens often, ask the database administrator to increase the LOGICAL_READS_PER_SESSION limit of the user profile.

ORA-02395 exceeded call limit on IO usage
Cause: An attempt was made to exceed the maximum I/O for a call, a parse, execute, or fetch, allowed by the LOGICAL_READS_PER_CALL clause of the user profile.
Action: If this happens often, ask the database administrator to increase the LOGICAL_READS_PER_CALL limit of the user profile.

ORA-02396 exceeded maximum idle time, please connect again
Cause: A user has exceeded the maximum time allowed to remain idle.
Action: The user must reconnect to the database.

The ORA-02397 exceeded PRIVATE_SGA limit, you are being logged off
Cause: This error occurs only when using a multi-threaded server.
Action: Contact the database administrator to expand the PRIVATE_SGA limit.