How to limit Database Resources per Session in Oracle?


You want to limit the amount of resources a user can consume in your database.


To limit the resources, we can follow below steps.

We can use below SQL statement to view the current setting of RESOURCE_LIMIT in our database.

select name, value from v$parameter where name='resource_limit';

Create a profile to limit the resources and assign it to a user. It won’t limit the CPU utilization though.


CREATE PROFILE test_profile LIMIT    SESSIONS_PER_USER          2    CPU_PER_SESSION            UNLIMITED    CPU_PER_CALL               300000    CONNECT_TIME               45    IDLE_TIME                  15    LOGICAL_READS_PER_SESSION  DEFAULT    LOGICAL_READS_PER_CALL     1000    PRIVATE_SGA                15K    FAILED_LOGIN_ATTEMPTS      5    PASSWORD_LIFE_TIME         60    PASSWORD_GRACE_TIME        10    PASSWORD_LOCK_TIME         1    PASSWORD_REUSE_TIME        10    PASSWORD_REUSE_MAX         1 ;


SESSIONS_PER_USER   -- Specify the number of concurrent sessions to which you want to limit the user. CPU_PER_SESSION     -- Specify the CPU time limit for a session, expressed in hundredth of seconds. CPU_PER_CALL        -- Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in hundredths of seconds. Need to increase this or not required to mention CONNECT_TIME        -- Specify the total elapsed time limit for a session, expressed in minutes. IDLE_TIME           -- Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit. LOGICAL_READS_PER_SESSION   -- Specify the permitted number of data blocks read in a session, including blocks read from memory and disk. PRIVATE_SGA                 -- Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA). Please refer to size_clause for information on that clause. FAILED_LOGIN_ATTEMPTS       -- Specify the number of failed attempts to log in to the user account before the account is locked PASSWORD_LIFE_TIME          -- Specify the number of days the same password can be used for authentication. PASSWORD_GRACE_TIME         -- Specify the number of days after the grace period begins during which a warning is issued and login is allowed. If the password is not changed during the grace period, the password expires.   PASSWORD_LOCK_TIME          -- Specify the number of days an account will be locked after the specified number of consecutive failed login attempts.   PASSWORD_REUSE_TIME         --specifies the number of days before which a password cannot be reused. PASSWORD_REUSE_MAX          --specifies the number of password changes required before the current password can be reused

When execute the test_profile would be have been created.

Once the profile is created, we can now assign it to a user. In the next example, user test is assigned the test_profile:

alter user test profile test_profile;

Oracle database profiles are used for couple of reasons, Setting resource limits and Enforcing password security settings.

When creating a user, if no profile is specified, the DEFAULT profile is assigned to the newly created user. We can modify a profile with the ALTER PROFILE statement. We can override the DEFAULT profile to limit the CPU_PER_SESSION to 360000 (in hundredths of seconds).

alter profile default limit cpu_per_session 360000;

A profile is also used to enforce password security settings, For example, say you wanted to alter the DEFAULT profile so that there was a cap on the maximum number of days a password can be used. This next line of code sets the PASSWORD_LIFE_TIME of the DEFAULT profile to 90 days.

alter profile default limit password_life_time 90;

The PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX settings must be used in conjunction. If you specify an integer for one parameter and then UNLIMITED for the other parameter, the current password can never be reused.

If you want to specify that the DEFAULT profile password has to be changed 10 times within 100 days before it can be reused, use a line of code similar to this:

alter profile default limit password_reuse_time 100 password_reuse_max 10;

Updated on: 05-Dec-2020

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started