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
