Sunday, April 20, 2008

User Resource Limits in Oracle

In oracle you can set limits on the amount of various system resources available to each user. By doing so, you can prevent the uncontrolled consumption of valuable system resources such as CPU time.

Types of System Resources and Limits
---------------------------------------------
Oracle can limit the use of several types of system resources. In general, you can control each of these resources at the session level, the call level, or both.

A)Session Level
-----------------

•If a user exceeds a session-level resource limit, then Oracle terminates (rolls back) the current statement and returns a message indicating that the session limit has been reached.

•All previous statements in the current transaction are intact, and the only operations the user can perform are COMMIT, ROLLBACK, or disconnect.

•All other operations produce an error. Even after the transaction is committed or rolled back, the user can accomplish no more work during the current session.

Session level resource limitation is set by CPU_PER_SESSION, LOGICAL_READS_PER_CALL

B)Call Level
------------------

•If a user exceeds a call-level resource limit, then Oracle halts the processing of the statement, rolls back the statement, and returns an error.

•Unlike session level, all previous statements of the current transaction remain intact, and the user's session remains connected.
Call level resource limitation is set by CPU_PER_CALL, LOGICAL_READS_PER_CALL

Both session level and call level limitation can be set by

ALTER PROFILE profile_name LIMIT resource_name value;

Other Resources:
--------------

•You can limit the number of concurrent sessions for each user. Each user can create only up to a predefined number of concurrent sessions. This is set by the resource name SESSIONS_PER_USER.

•You can limit the idle time for a session. If the time between Oracle calls for a session reaches the idle time limit, then the current transaction is rolled back, the session is aborted, and the resources of the session are returned to the system. The next call receives an error that indicates the user is no longer connected to the instance. This limit is set by the resource name IDLE_TIME.

•You can limit the elapsed connect time for each session. If a session's duration exceeds the elapsed time limit, then the current transaction is rolled back, the session is dropped, and the resources of the session are returned to the system. This limit is set as a number of elapsed minutes.For performance reason oracle check elapsed idle time or elapsed connection time after every few minutes.This elapsed connect time limitation is maintained by resource name CONNECT_TIME.


•You can limit the amount of private SGA spacefor a session. This limit is only important in systems that use the shared server configuration. Otherwise, private SQL areas are located in the PGA. This limitation is maintained by resource name PRIVATE_SGA.

All limitations can be set by assign a profile to the specified user and then altering the resource. ALTER PROFILE profile_name LIMIT resource_name value;

For example:
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 5;
Related Documents:
Profile in Oracle
Posted by Arju at 10:54 PM Labels: ,
Reactions: 

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)