Understanding Oracle User Profile Resource Parameters

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