Most of our Oracle applications are being upgraded to 11g version nowadays. At this time it is important to discuss some features in the Oracle 11g Default Profile:
We know that any user created in a database needs to be assigned to a profile. If not, the user will be assigned to DEFAULT profile. Lets see some features in Oracle 11g Default Profile.
The limits of Oracle 10g and 11g DEFAULT profile is given below:
Resource Name Oracle 10g Oracle 11g
FAILED_LOGIN_ATTEMPTS 10 10
PASSWORD_GRACE_TIME UNLIMITED 7 (days)
PASSWORD_LIFE_TIME UNLIMITED 180 (days)
PASSWORD_LOCK_TIME UNLIMITED 1 (days)
PASSWORD_REUSE_MAX UNLIMITED UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED UNLIMITED
COMPOSITE_LIMIT UNLIMITED UNLIMITED
SESSIONS_PER_USER UNLIMITED UNLIMITED
CPU_PER_SESSION UNLIMITED UNLIMITED
CPU_PER_CALL UNLIMITED UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED UNLIMITED
IDLE_TIME UNLIMITED UNLIMITED
CONNECT_TIME UNLIMITED UNLIMITED
PRIVATE_SGA UNLIMITED UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL NULL
This can be obtained by using the query :
SELECT profile,resource_name, limit
Note that PASSWORD_LIFE_TIME in both versions . In 10g it is set to UNLIMITED, ie the password of the user set to default profile never expires once set. But in 11g, it expires every 180 days.
So once the 11g upgrade is done Oracle will start enforcing the password lifetime of 180 days, rather than the previous default of unlimited.
We have to be very careful as it may lock certain critical users after 180days in the database which are assigned DEFAULT profile. So considering the implications, users have to be given another profile or this setting has to be changed.
Article by : Divya