Use resource_limit and profile to restrict user connections

Database performance is an eternal topic, that is, how to use less resources to achieve more efficient performance. The Oracle system parameter RESOURCE_LIMIT is a parameter used to control the user's usage of database resources. It is enabled when the value is true. Otherwise, it is disabled. This parameter can be used with profile to control the use of multiple resources, such as CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION,
PRIVATE_SGA and so on to save resources to achieve efficient performance. This article describes data resource restrictions and demonstrates IDLE_TIME and SESSIONS_PER_USER usage.

1. Steps for database resource restrictions
Implemented
* Setting RESOURCE_LIMIT = TRUE in the database startup parameter file (spfile or pfile)
* Creating or modifying existing user profiles (DBA_PROFILES) to have one or more resource limit
* Assigning a profile to a user whose resources are wished to be limited

It cocould happen that if the idle_time has been set on the DEFAULT profile, this can lead to an MTS dispatchers being set to 'sniped' and then getting 'cleaned' via the shell script.

The removal of the dispatcher will result in other sessions 'dying '. in that case, If you are to implement resource limits, may be advisable to create new profiles that be assigned to users and not to change the characteristics of DEFAULT.
Alternatively, if you do change DEFAULT, ensure that all the properties that you have affected have been fully tested in a development environment.

Actions completed after the user exceeds the limit
When a resource limit is exceeded (for example IDLE_TIME)... PMON does the following
* Mark the V $ SESSION as SNIPED
* Clean up the database resources for the session
* Remove the V $ SESSION entry

2. Resource Restriction Configuration

-- Demo Environment
SQL> select * from v $ version where rownum <2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

-- View resource_limit
SQL> show parameter resource_limit

NAME TYPE VALUE
-----------------------------------------------------------------------------
Resource_limit boolean FALSE

-- Change resource_limit to true.
SQL> alter system set resource_limit = true;

System altered.

SQL> show parameter resource_limit

NAME TYPE VALUE
-----------------------------------------------------------------------------
Resource_limit boolean TRUE

-- Create a profile. Its idle_time is 3 minutes.
SQL> create profile app_user limit idle_time 3;

Profile created.

-- Modify the profile to limit that each user can open only one session
SQL> alter profile app_user limit sessions_per_user 1;

Profile altered.

-- Assign a user to a specific profile
SQL> alter user scott profile app_user;

User altered.

-- View the created profile. You can set or modify RESOURCE_NAME in the query result.
SQL> select * from dba_profiles where profile = 'app _ user ';

PROFILE RESOURCE_NAME RESOURCE LIMIT
--------------------------------------------------------------------------------------------------------------
APP_USER COMPOSITE_LIMIT KERNEL DEFAULT
APP_USER SESSIONS_PER_USER KERNEL 1
APP_USER CPU_PER_SESSION KERNEL DEFAULT
APP_USER CPU_PER_CALL KERNEL DEFAULT
APP_USER LOGICAL_READS_PER_SESSION KERNEL DEFAULT
APP_USER LOGICAL_READS_PER_CALL KERNEL DEFAULT
APP_USER IDLE_TIME KERNEL 3
APP_USER CONNECT_TIME KERNEL DEFAULT
APP_USER PRIVATE_SGA KERNEL DEFAULT
APP_USER FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
APP_USER PASSWORD_LIFE_TIME PASSWORD DEFAULT
APP_USER PASSWORD_REUSE_TIME PASSWORD DEFAULT
APP_USER PASSWORD_REUSE_MAX PASSWORD DEFAULT
APP_USER PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
APP_USER PASSWORD_LOCK_TIME PASSWORD DEFAULT
APP_USER PASSWORD_GRACE_TIME PASSWORD DEFAULT

16 rows selected.

1 2 Next Page