Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to limit Database Resources per Session in Oracle?
Problem:
You want to limit the amount of resources a user can consume in your database.
Solution
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.
Example
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 ;
Output
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;
2K+ Views
