Problem Description
——————-
The DBA needs to know which values are assigned to limits set to DEFAULT
for a given profile. Querying DBA_PROFILES does not display the actual values
but rather lists DEFAULT for those LIMITs.
Solution Description
——————–
From the SQL*Plus prompt, type:
SQL> select * from dba_profiles where profile = ‘DEFAULT’;
PROFILE RESOURCE_NAME RESOURCE_TYPE 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 UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED
DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED
16 rows selected.
The value of the LIMIT column corresponds to the “default” value.
You can change the default profile by changing the value in the LIMIT column.
SQL> ALTER PROFILE DEFAULT limit
Ex:
SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LOCK_TIME 1;
When you create a new profile, you must set at least one limit. It automatically
uses the default limits for all other resources not explicitly initialized.
Ex:
SQL> CREATE PROFILE test LIMIT connect_time 60;
Profile created.
SQL> select * from dba_profiles where profile = ‘TEST’;
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
———- —————————- ————- ——————–
TEST COMPOSITE_LIMIT KERNEL DEFAULT
TEST SESSIONS_PER_USER KERNEL DEFAULT
TEST CPU_PER_SESSION KERNEL DEFAULT
TEST CPU_PER_CALL KERNEL DEFAULT
TEST LOGICAL_READS_PER_SESSION KERNEL DEFAULT
TEST LOGICAL_READS_PER_CALL KERNEL DEFAULT
TEST IDLE_TIME KERNEL DEFAULT
TEST CONNECT_TIME KERNEL 60
TEST PRIVATE_SGA KERNEL DEFAULT
TEST FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT
TEST PASSWORD_LIFE_TIME PASSWORD DEFAULT
TEST PASSWORD_REUSE_TIME PASSWORD DEFAULT
TEST PASSWORD_REUSE_MAX PASSWORD DEFAULT
TEST PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
TEST PASSWORD_LOCK_TIME PASSWORD DEFAULT
TEST PASSWORD_GRACE_TIME PASSWORD DEFAULT
Only the resource CONNECT_TIME has a value of 60 in the column LIMIT. All other
values refer to the DEFAULT limit – which means you have to look at the DEFAULT
profile to see which value is under LIMIT by using:
SQL> select * from dba_profiles where profile = ‘DEFAULT’;
Explanation
———–
If you only query DBA_PROFILES, this does not give sufficient information for
the default profile values. You need to query DBA_PROFILES where profile = ‘DEFAULT’
in order to see the default values.