• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle Database: How To Use PROFILES To Limit User Resources

by admin

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.

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to move ASM spfile from External Redundancy To Normal Redundancy in version 12.1.0.2 and above
  2. How to change static parameters through SPFILE parameter file in Oracle Database
  3. Oracle RMAN interview questions
  4. How to Set Timeout for WebLogic Web Service Client (JAX-WS and JAX-RPC)
  5. How to get the DDL for indexes using dbms_metadata
  6. ORA-19554: error allocating device, device type: SBT_TAPE, device name:
  7. Empty Directories in the Flash Recovery Area (FRA) are not deleted
  8. How to Disable Oracle Net Tracing without stopping server process
  9. How To Change Timezone for Oracle Grid Infrastructure
  10. How To Disable Advanced Analytics in Oracle Database 12c?

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright