Detailed explanation of password expiration problem in Oracle 11g _oracle

There are generally two possible reasons for password expiration:

One, due to the default in Oracle set "Password_life_time=180 days" in the defaults profile.

Second, the default profile in Oracle is set to "failed_login_attempts=10", which causes this problem when the number of password errors reaches the set value.

The first case resolution is as follows:

1, to see what kind of user profile strategy, is generally default:

Copy Code code as follows:

Sql> select Username,profile from Dba_users;
USERNAME profile
------------------ -----------------
Mgmt_view DEFAULT
SYS DEFAULT
SYSTEM DEFAULT
Ekpuser DEFAULT

2. View password expiration settings for the specified profile (such as default):

Copy Code code as follows:

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, the password will expire from the default 180 days to modify to "unrestricted":

Copy Code code as follows:

Sql> ALTER profile DEFAULT LIMIT Password_life_time Unlimited;
Profile Altered

You do not need to restart the database after the modification, and will take effect immediately.

4, after the modification, has not been prompted ORA-28002 warning account will not encounter the same prompts, the account has been prompted to change the password again, for example, as follows:

Copy Code code as follows:

Sqlplus/as SYSDBA
sql> alter user SMSC identified by < original password >----No need to change the new password
sql> alter user system identified by root;
Sql> alter user Scott identified by Tiger;

oracle11g Startup Parameters Resource_limit whether set to False or true, the password validity period is valid and must be modified in the above way. The account name above is changed according to the account name that you are actually using.

The second solution is as follows:

1, look at the user's proifle, which is generally default:

Copy Code code as follows:

Sql> SELECT username,profile from Dba_users;

2. View password expiration settings for the specified profile (such as default):

Copy Code code as follows:

Sql> SELECT * from dba_profiles s WHERE s.profile= ' DEFAULT ' and resource_name= ' failed_login_attempts ';

3, the number of attempts to logon failed from the default 10 times to "unrestricted":

Copy Code code as follows:

Sql> ALTER profile DEFAULT LIMIT Failed_login_attempts Unlimited;

4, after the modification, has not been prompted to ORA-28000 warning users will not encounter the same hint;
Accounts that have been locked must be unlocked, for example:

Copy Code code as follows:

Sqlplus/as SYSDBA
sql> alter user SMSC identified by Oracle account unlock;

5, after the modified default profile should be as follows:

Copy Code code 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 the modification, and will take effect immediately.
If the ORA-28000 is locked error appears.

Copy Code code as follows:

Alter user username account unlock;