The purpose of the post is to provide step-by-step instructions on how to create a database profile that can be used to limit the use of database resources. In this example, the IDLE_TIME resource is discussed in detail and can be applied to other database 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 the 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:
SQL> ALTER SYSTEM SET RESOURCE_LIMIT = TRUE SCOPE=BOTH;
With pfile:
SQL> ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
8. Alternatively, starting with Oracle 10g, Resource Manager can be used to set an idle time for sessions.