Changes to default password expiration days and locks in Oracle 11g

There are generally two possible reasons for password expiration:

First, because the default profile in Oracle is set to "password_life_time=180 days".

second, because the default profile in Oracle is set to "failed_login_attempts=10 times", when the number of password errors reached the set value will cause this problem.

The first case resolution is as follows:

1. Check which profile policy is used by the user, usually default:
Sql> select Username,profile from Dba_users;
USERNAME profile
------------------ -----------------
Mgmt_view DEFAULT
SYS DEFAULT
SYSTEM DEFAULT
Ekpuser DEFAULT

2. View the password expiration settings for the specified profile (such as default):
Sql> Select * from dba_profiles s Where s.profile= ' DEFAULT ' and resource_name= ' password_life_time ';
Profile Resource_name Resource_type LIMIT
------------------------------ ----------------------- ------------ --------
DEFAULT Password_life_time PASSWORD 180

3. Change the password validity period from the default of 180 days to "unlimited":
Sql> ALTER profile DEFAULT LIMIT password_life_time UNLIMITED;
Profile Altered
You do not need to restart the database after you modify it, it takes effect immediately.

4, after the modification, has not been prompted ORA-28002 warning account will not encounter the same prompt; The account that has been prompted must change the password again, for example:
Sqlplus/as SYSDBA
sql> alter user SMSC identified by < original password >----do not change the new password
sql> alter user system identified by root;
Sql> alter user Scott identified by Tiger;
oracle11g startup parameter Resource_limit whether it is set to False or true, the password validity period is valid and must be modified in the above manner. The account name above is changed according to the account name you are actually using.

The second situation is resolved as follows:

1, check the user's proifle is which, is generally default:
Sql> SELECT username,profile from Dba_users;

2. View the password expiration settings for the specified profile (such as default):
Sql> SELECT * from dba_profiles s WHERE s.profile= ' DEFAULT ' and resource_name= ' failed_login_attempts ';

3, the number of attempts to log on failed to change from the default 10 times to "unlimited":
Sql> ALTER profile DEFAULT LIMIT failed_login_attempts UNLIMITED;

4, after the modification, has not been prompted ORA-28000 warning users will not encounter the same prompt;
An account that has been locked must be unlocked, for example:
Sqlplus/as SYSDBA
sql> alter user SMSC identified by Oracle account unlock;

5. After the modified default profile should be as follows:
Sql> SELECT * from Dba_profiles WHERE dba_profiles.profile= ' DEFAULT ';
Profile Resource_name Resource_type LIMIT
------------ ----------------------- --------------- ----------------------
DEFAULT failed_login_attempts PASSWORD UNLIMITED
DEFAULT password_life_time PASSWORD UNLIMITED
You do not need to restart the database after you modify it, it takes effect immediately.
If the ORA-28000 the account is locked. Error
Alter user username account unlock;

Changes to default password expiration days and locks in Oracle 11g