ORA-28002: the password will expire within 7 days

ORA-28002: the password will expire within 7 days

Cause: The user's account is about to about to expire within 7days  and the password needs
to be changed.
Action: Change the password.

Solutions:-
sqlplus soumya/soumya
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 2 08:52:52 2015
ERROR:
ORA-28002: the password will expire within 7 days
1. Simply change the password to avoid it temporary :-

SQL> password
Changing password for SOUMYA
Old password:
New password:
Retype new password:
Password changed

2. Set PASSWORD_LIFE_TIME of the profile assigned user to UNLIMITED then change the password to avoid it permanently:-

SQL> SELECT PROFILE FROM dba_users WHERE username = 'SOUMYA';

PROFILE
------------------------------
DEFAULT

SQL> SELECT  LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

LIMIT
----------------------------------------

60


SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Profile altered.


SQL> SELECT  LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME';

LIMIT
----------------------------------------
UNLIMITED

You may notice that even after setting the password expiry to unlimited you are still getting the “ERROR: ORA-28002: the password will expire” message. I’m guessing this is due to additional processes that Oracle does in the background for checking password age etc. So this forces us to “reset” the password to it’s current value to remove the error.

SQL> alter user SYSTEM identified by "password";(PASSWORD OF SOUMYA)

User altered.