Resource control
Each user profile has eight individual resource limits and one composite limit that can be specified. For each limit, the possible values are an integer or the keywords UNLIMITED or DEFAULT. When DEFAULT is used, the value is replaced by the corresponding value in the DEFAULT profile.
The limits are summarized here:
- COMPOSITE_LIMIT
Weighed sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA. The weights applied to each parameter are specified using the ALTER RESOURCE COST command. Parameters that are not specified take a weighted value of 0. Initially, each parameter has a weighted value of 0. If this value is exceeded, the current operation is aborted, an error message is returned, and the user is only able to commit or roll back and then exit.
- CPU_PER_SESSION
Total amount of CPU that can be used by the session. Value is in hundredths of a second. If this value is exceeded, the current operation is aborted, an error message is returned, and the user is only able to commit or roll back and then exit.
- CPU_PER_CALL
Total amount of CPU that can be used by any one parse, execute, or fetch call. Value is in hundredths of a second. This can be used to terminate runaway queries. If this value is exceeded, the current operation is aborted, an error message is returned, and the user is only able to commit or roll back and then exit.
- CONNECT_TIME
Total amount of elapsed time for which a given connection can be maintained. Value is in minutes. This can be used to ensure that a given session does not remain connected to the database indefinitely. If this value is exceeded, the current transaction is rolled back and the user receives an error on the next SQL statement.
- IDLE_TIME
Total amount of elapsed time allowed between any two SQL statements. Value is in minutes. This is used to time out inactive sessions, either to free up session slots or to limit exposure when someone leaves an active session on their workstation. If this value is exceeded, the current transaction is rolled back and the user receives an error on the next SQL statement.
- LOGICAL_READS_PER_SESSION
Total number of disk blocks (either from memory or disk) that can be read by the session. Value is in blocks. This is used to place a total limit on the amount of I/O that can be done by a session. If this value is exceeded, the current operation is aborted, an error message is returned, and the user is only able to commit or roll back and then exit.
- LOGICAL_READS_PER_CALL
Total number of disk blocks (either from memory or disk) that can be read by a parse, execute, or fetch call. Value is in blocks. This is used to place a limit on the amount of I/O that can be performed by a given call in an attempt to identify and stop runaway queries. If this value is exceeded, the current operation is aborted, an error message is returned, and the user is only able to commit or roll back and then exit.
- PRIVATE_SGA
Total amount of private space that can be allocated to the session out of the shared pool area for private SQL and PL/SQL areas. This does not apply to the shared SQL or PL/SQL areas. Value is in bytes, but the K and M suffixes are accepted to specify kilobytes or megabytes. This parameter is only used in a Multi-Threaded Server (MTS) system.
- SESSIONS_PER_USER
Total number of concurrent sessions the user can have active at a given time. Value is in sessions. This value is normally used to limit a user to a specified maximum number of concurrent sessions. The SESSIONS_PER_USER parameter not only limits the number of concurrent sessions, but also limits the number of Parallel Query sessions that can be used. If the value of SESSIONS_PER_USER is less than the degree of parallelism used by the query, an ORA-2391 error will be generated.