Resource Parameters in Oracle

Back to: Oracle DBA Tutorials

Resource Parameters in Oracle with Examples

In this article, I am going to discuss Resource Parameters in Oracle with Examples. Please read our previous article where we discussed User Password in Oracle with Examples. Until now we understood that a user profile controls resource limits. We already learned about password parameters. Now, we will learn about resource parameters.

Resource Parameters in Oracle:

In a profile, we can control

• CPU Resources may be limited on a per-session or per-call basis. There are two parameters for this resource
  • CPU_PER_SESSION
  • CPU_PER_CALL

Let us assume that the value of the parameter CPU_PER_CALL is defined as 3000 then the single call made by the user cannot consume more than 30 seconds of CPU time.

• For network and memory resources you can specify the following
  • Connect time
  • Idle time
  • Concurrent sessions
  • Private SGA.
• Disk I/o Resources which limit the amount of data a user can read at the per session level or per call level

All these parameters can be configured in the profiles section.

Profiles cannot impose resource limitations on users unless the RESOURCE_LIMIT initialization parameter is set to TRUE. In order to use all these parameters, the parameters RESOURCE_LIMIT must be set to TRUE. The RESOURCE_LIMIT at its default value of FALSE, the profile resource limitations are ignored. Even if the dba uses all the resource parameters but the RESOURCE_LIMIT is set to false then all the resource parameters that the dba has configured become useless.

Profiles also allow compose limits, which are based on weighted combinations of CPU/session, reads/session, connect time, and private SGA.

From the above image, we can see that SESSIONS_PER_USER is set to unlimited which means the user can open multiple sessions and CPU_PER_CALL is set to 3000. A single session cannot last for more than 45 minutes.

In the next article, I am going to discuss the Creation of a Common Profile in Oracle with Examples. Here, in this article, I try to explain Resource Parameters in Oracle with Examples. I hope you enjoy this Resource Parameters in Oracle article.