Managed resources parameter in profile for User in Oracle

Managed resources parameter in profile for User in Oracle

In user profile Resource parameter include the cpu resource, cpu call, logical read, private Sga, idle time, connect time parameter.

Resource_parameters in Profile:

[SESSIONS_PER_USER          n|UNLIMITED|DEFAULT]
[CPU_PER_SESSION            n|UNLIMITED|DEFAULT]     
[CPU_PER_CALL               n|UNLIMITED|DEFAULT]            
[CONNECT_TIME               n|UNLIMITED|DEFAULT]
[IDLE_TIME                  n|UNLIMITED|DEFAULT]
[LOGICAL_READS_PER_SESSION  n|UNLIMITED|DEFAULT]  
[LOGICAL_READS_PER_CALL     n|UNLIMITED|DEFAULT]
[COMPOSITE_LIMIT            n|UNLIMITED|DEFAULT]
[PRIVATE_SGA                n [K|M]|UNLIMITED|DEFAULT]

Check the user belong to which profile

select profile from dba_users where username = 'TEST';

Profile
------------
DEFAULT

Check resource_name and limit defined for the profile.
It include both resource parameter and password parameter. We are working on resource parameter in this blog.

select resource_name, limit from DBA_PROFILES where profile = 'DEFAULT';

RESOURCE_NAME LIMIT
-------------------------------- ----------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME 1
PASSWORD_GRACE_TIME 7
INACTIVE_ACCOUNT_TIME UNLIMITED

Meaning of Resource Name
COMPOSITE_LIMIT: Specify the total resource cost for a session, expressed in service units. The total service units as a weighted sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.
SESSIONS_PER_USER: Number of concurrent sessions for the user.
CPU_PER_SESSION: CPU time limit for a session(hundredth of seconds)
CPU_PER_CALL: CPU time limit for a call (a parse, execute, or fetch)(hundredths of seconds)
CONNECT_TIME: Total elapsed time limit for a session(minutes)
IDLE_TIME: Permitted periods of continuous inactive time during a session(minutes).
LOGICAL_READS_PER_SESSION: Permitted number of data blocks read in a session, including blocks read from memory and disk.
LOGICAL_READS_PER_CALL: Permitted number of data blocks read for a call to process a SQL statement (a parse, execute, or fetch).
PRIVATE_SGA: Amount of private space a session can allocate in the shared pool of the system global area (SGA).

Alter the Resource parameter in profile

---Alter the Composite limit which is total weighted sum of units.
ALTER PROFILE DEFAULT LIMIT COMPOSITE_LIMIT 5000000;

---Alter the SESSIONS_PER_USER
ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 2;

--Total elapsed time for session
ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 2;

--Alter CPU_PER_SESSION cpu usage per session
ALTER PROFILE DEFAULT LIMIT CPU_PER_SESSION 2;

--Alter CPU timelimit
ALTER PROFILE DEFAULT LIMIT CPU_PER_CALL 20000;

--Alter CPU CONNECT_TIME
ALTER PROFILE DEFAULT LIMIT CONNECT_TIME 1;

--Alter ideal time limit for session inactive
ALTER PROFILE DEFAULT LIMIT IDLE_TIME 1;

Refer:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6010.htm

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.