Mir Sayeed Hassan – Oracle Blog

Overview of Profile Management with real-time scenario in Oracle Database 11gR2 (11.2.0.4)

A Profile is a database object & it’s named set of resource limits to such as restrict database usage by a system user.

Examples of resources that need to be managed are Disk storage space, I/O bandwidth to run queries, CPU power, CPU time and connect time.

Profile are assigned to the user in database as create user or alter user.

Note: The user of database accounts can have only a single profile & the default profile can be created which already exists within Oracle database as “DEFAULT”.

If you are assigning a new profile to a user account to earlier profile, the existing profile will be invalid and profiles cannot be assigned to roles or other profiles as per the oracle.

Below are the few system privileges for profile such as:

Create a profile, alter a profile, create & drop a profile.

The major benefits of profile are to enforce a limit on resource utilization using resource limit parameters.

Resource Parameters as per the Oracle documents are:

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.

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.

LOGICAL_READS_PER_CALL – Specify the permitted the number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).

PRIVATE_SGA – Specify the amount of private space a session can allocate in the shared pool of the system global area (SGA), expressed in bytes.

COMPOSITE_LIMIT – Specify the total resource cost for a session, expressed in service units. Oracle Database calculates the total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.

Create a Profile

Check the status of database

SQL> select INSTANCE_NAME,VERSION,open_mode,status from v$instance,v$database;

INSTANCE_NAME    VERSION           OPEN_MODE            STATUS
----------- ----------------- -------------------- ------------
testdb123        11.2.0.4.0        READ WRITE           OPEN

Check the resource limit

SQL> show parameter resource_limit

NAME                                 TYPE        VALUE
--------------- ----------- ------------------------------
resource_limit                       boolean     FALSE

Note: By default the database resource limit is false, we need to enable it by using alter system command to take effect in database.

Enable the resource limit to TRUE

SQL> alter system set resource_limit=true;
System altered.

Verify

SQL> show parameter resource_limit
NAME                                 TYPE        VALUE
------------- ----------- ------------------------------
resource_limit                       boolean     TRUE

Now Create a Profile with limitation as per your requirement

SQL> create profile mir_profile limit
     sessions_per_user 2
     connect_time 5
     idle_time 4;

Profile created.

The above example describe as simple profile

SESSIONS_PER_USER – Here it able to open 2 sessions concurrent on server

CONNECT_TIME          – Here it will keep the session online until the value specified. (mins)

IDLE_TIME                   – The session will be terminate automatically after the time specified. (mins)

Now will create a user mir_test to check the functionality of above profile created.

SQL> create user mir_test_profile identified by mirtestprofile;
User created.

Set this user to use the created profile

SQL> alter user mir_test_profile profile mir_profile;
User altered.

OR

SQL> create user mir_test_profile identified by mirtestprofile profile mir_profile;
User created.

SQL> select username,profile from dba_users where username='MIR_TEST_PROFILE';

USERNAME                       PROFILE
------------- ------------------------------
MIR_TEST_PROFILE               MIR_PROFILE

Note: I have set the “mir_profile” to user “mir_test_profile”

Test the Sessions_per_user:

I’ll open the 2 sessions with the user “mir_test_profile” & I’ll try to open the 3^rd session then it will throws an error as shown below.

Test the Idle Time:

Here the idle time was set as 4mins, if it’s exceed Oracle server kill session.

Check the resource parameter assign for profile.

SQL> select * from dba_profiles where profile='MIR_PROFILE';

PROFILE                        RESOURCE_NAME                    RESOURCE LIMIT
------------------------------ -------- ----------------------------------------
MIR_PROFILE                    SESSIONS_PER_USER                KERNEL   2
MIR_PROFILE                    IDLE_TIME                        KERNEL   4
MIR_PROFILE                    CONNECT_TIME                     KERNEL   5

Altering Profile

We can alter an existing profile by using the alter command, the user dba should have the alter privilege to perform this operation.

Example: alter the above profile

SQL> alter profile mir_profile limit
     sessions_per_user 5
     connect_time 10
     idle_time 15;

Profile altered.

Dropping Profile

 If you don’t want to use the above profile anymore, then you can go ahead and drop it.

Note: Default profile cannot be dropped, The CASCADE clause revokes the profile from any user account to which it was assigned

SQL> drop profile mir_profile;
drop profile mir_profile
*
ERROR at line 1:
ORA-02382: profile MIR_PROFILE has users assigned, cannot drop without CASCADE

SQL> drop profile mir_profile cascade;
Profile dropped.

SQL> select username,profile from dba_users where username='MIR_TEST_PROFILE';

USERNAME                       PROFILE
-----------------------------------------
MIR_TEST_PROFILE               DEFAULT

=====Hence tested & verified in our test env=====