How To Use PROFILES To Limit User Resources

You have a group of users that do not always disconnect from the database when they are done, or, they leave their connection idle for long periods of time. You want to eliminate these connections to make more connections available to other users.


You can create a database profile to limit use of database resources. To prevent users from leaving idle processes connected, create a database profile that sets the IDLE_TIME resource. Any user assigned this profile cannot exceed the IDLE_TIME specified in the profile. You can define multiple profiles which specify different IDLE_TIME limits based on site-specific needs. The IDLE_TIME resource is specified in minutes.


1) Log to the database as SYSDBA.


SQL> CONNECT /AS SYSDBA
Connected.

2) List the profiles currently on the system.

SQL> SELECT * FROM DBA_PROFILES;

SQL> /

PROFILE              RESOURCE_NAME                  RESOURCE LIMIT
-------------------- ------------------------------ -------- --------------------
DEFAULT              COMPOSITE_LIMIT                KERNEL   UNLIMITED
DEFAULT              SESSIONS_PER_USER              KERNEL   UNLIMITED
DEFAULT              CPU_PER_SESSION                KERNEL   UNLIMITED
DEFAULT              CPU_PER_CALL                   KERNEL   UNLIMITED
DEFAULT              LOGICAL_READS_PER_SESSION      KERNEL   UNLIMITED
DEFAULT              LOGICAL_READS_PER_CALL         KERNEL   UNLIMITED
DEFAULT              IDLE_TIME                      KERNEL   UNLIMITED
DEFAULT              CONNECT_TIME                   KERNEL   UNLIMITED
DEFAULT              PRIVATE_SGA                    KERNEL   UNLIMITED
DEFAULT              FAILED_LOGIN_ATTEMPTS          PASSWORD 10
DEFAULT              PASSWORD_LIFE_TIME             PASSWORD 180

PROFILE              RESOURCE_NAME                  RESOURCE LIMIT
-------------------- ------------------------------ -------- --------------------
DEFAULT              PASSWORD_REUSE_TIME            PASSWORD UNLIMITED
DEFAULT              PASSWORD_REUSE_MAX             PASSWORD UNLIMITED
DEFAULT              PASSWORD_VERIFY_FUNCTION       PASSWORD NULL
DEFAULT              PASSWORD_LOCK_TIME             PASSWORD 1
DEFAULT              PASSWORD_GRACE_TIME            PASSWORD 7

3. List the Oracle users and the profiles assigned to them.

SQL> SELECT USERNAME, USER_ID, PROFILE FROM DBA_USERS;

USERNAME                          USER_ID PROFILE
------------------------------ ---------- --------------------
LBACSYS                                72 DEFAULT
MGMT_VIEW                              76 DEFAULT
SYS                                     0 DEFAULT
DVF                                    86 DEFAULT
DBSNMP                                 30 DEFAULT
SYSMAN                                 74 DEFAULT
ZIOB                                  116 DEFAULT
TNE                                    99 DEFAULT
SYSTEM                                  5 DEFAULT

...

OLAPSYS                                61 DEFAULT
ORACLE_OCM                             21 DEFAULT
XS$NULL                        2147483638 DEFAULT
MDDATA                                 65 DEFAULT
DIP                                    14 DEFAULT
APEX_PUBLIC_USER                       78 DEFAULT

USERNAME                          USER_ID PROFILE
------------------------------ ---------- --------------------
SPATIAL_CSW_ADMIN_USR                  70 DEFAULT
SPATIAL_WFS_ADMIN_USR                  67 DEFAULT

46 rows selected.

4. If a profile other than DEFAULT does not exist, create a database resource profile. The statement below creates a profile called IDLETEST and limits the IDLE_TIME to 60 minutes.

SQL> CREATE PROFILE IDLETEST LIMIT IDLE_TIME 60;
Statement processed.

5. If a profile other than default exists, you can use that profile or create a new one. To use the existing profile, alter it. The statement below alters profile IDLETEST to limit IDLE_TIME to 90 minutes.

SQL> ALTER PROFILE IDLETEST LIMIT IDLE_TIME 90;
Statement processed.

6. Alter the user and assign them the profile with the resource limit.

SQL> ALTER USER SIDNEY PROFILE IDLETEST;
Statement processed.

SQL>  SELECT USERNAME, USER_ID, PROFILE FROM DBA_USERS;

USERNAME                          USER_ID PROFILE
------------------------------ ---------- --------------------
LBACSYS                                72 DEFAULT
MGMT_VIEW                              76 DEFAULT
SYS                                     0 DEFAULT
DVF                                    86 DEFAULT
HR                                     94 DEFAULT
JOE                                   100 DEFAULT
DVOWNER                                93 DEFAULT
SIDNEY                                122 IDLETEST

...

MDDATA                                 65 DEFAULT
DIP                                    14 DEFAULT

USERNAME                          USER_ID PROFILE
------------------------------ ---------- --------------------
APEX_PUBLIC_USER                       78 DEFAULT
SPATIAL_CSW_ADMIN_USR                  70 DEFAULT
SPATIAL_WFS_ADMIN_USR                  67 DEFAULT

7. After you set up the profile, you must either edit your INIT.ORA file or your stored configuration in OEM and set: RESOURCE_LIMIT=TRUE.

The parameter can be set dynamically using the ALTER SYSTEM command:

With spfile: ALTER SYSTEM SET RESOURCE_LIMIT = TRUE SCOPE=BOTH;
With text init.ora: ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

Setting Profile Resource Limits: Example

The following statement creates the profile app_user:

CREATE PROFILE app_user LIMIT 
   SESSIONS_PER_USER          UNLIMITED 
   CPU_PER_SESSION            UNLIMITED 
   CPU_PER_CALL               3000 
   CONNECT_TIME               45 
   LOGICAL_READS_PER_SESSION  DEFAULT 
   LOGICAL_READS_PER_CALL     1000 
   PRIVATE_SGA                15K
   COMPOSITE_LIMIT            5000000; 

If you assign the app_user profile to a user, the user is subject to the following limits in subsequent sessions:

• The user can have any number of concurrent sessions.
• In a single session, the user can consume an unlimited amount of CPU time.
• A single call made by the user cannot consume more than 30 seconds of CPU time.
• A single session cannot last for more than 45 minutes.
• In a single session, the number of data blocks read from memory and disk is subject to the limit specified in the DEFAULT profile.
• A single call made by the user cannot read more than 1000 data blocks from memory and disk.
• A single session cannot allocate more than 15 kilobytes of memory in the SGA.
• In a single session, the total resource cost cannot exceed 5 million service units. The formula for calculating the total resource cost is specified by the ALTER RESOURCE COST statement.
• Since the app_user profile omits a limit for IDLE_TIME and for password limits, the user is subject to the limits on these resources specified in the DEFAULT profile.

Setting Profile Password Limits: Example
The following statement creates the app_user2 profile with password limits values set:

CREATE PROFILE app_user2 LIMIT
   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LIFE_TIME 60
   PASSWORD_REUSE_TIME 60
   PASSWORD_REUSE_MAX 5
   PASSWORD_VERIFY_FUNCTION verify_function
   PASSWORD_LOCK_TIME 1/24
   PASSWORD_GRACE_TIME 10;

This example uses the default Oracle Database password verification function, verify_function.

 Note:

1- When you create a new profile, you must set at least one limit. It automatically 
uses the default limits for all other resources not explicitly initialized.

select * from dba_profiles where profile = 'IDLETEST'

2- A session can stay more than the time specified in profile as I just set the idle time for 1 minute but session idle time applied after 3 minutes. When idle time is applied the session does not go away from Oracle and v$session will be showing session's status as "SNIPED" . The session counts against the sessions parameter of DB and will not be released until the user comes back and attempts to access the session.