ORA-28002 means that the password of the user will expire soon in N days, although the user is allowable to connect to the database. The error reminds you that to change the password in advance.
SQL> conn hr/hr@orclpdb
ORA-28002: the password will expire within 7 days
The database warned us that the password will expired in 7 days with ORA-28002. In fact, password expiration is controlled by password life time (PASSWORD_LIFE_TIME) in user's profile, the shorter lifetime, the sooner users see ORA-28002.
Solutions to ORA-28002
Theoretically, once the grace time is triggered, it has no way to return, but we have some ideas for you to think of.
There're several ways to solve ORA-28002.
- Change Password
- Defer Expiration
The formal way to solve it is to change the password.
An unofficial way to solve it is to revert the expiration.
1. Change Password
As an user, you may change your password right away. To reuse the original password you forgot to set password, there will be more works to do.
Changing password for HR
Retype new password:
We can also use ALTER USER to change the password before expiration.
SQL> alter user hr identified by welcome1;
Where welcome1 is the explicit password, it's case-sensitive with or without double quotation marks.
For more ways to do it, you can also use various GUI tools to change the password.
2. Defer Expiration
An unsupported way is to postpone the expiration to a latter time and remove the grace time.
SQL> show user
USER is "SYS"
SQL> update user$ set ptime = sysdate, exptime = sysdate + 180, ltime = null, lcount = 0, astatus = 16, spare6 = systimestamp at time zone 'UTC' where name = 'HR';
1 row updated.
In the statement, we put off the expiration by adding 180 days from now and remove the grace time. There will be no more expiration message.
To have a never expired password (PASSWORD_LIFE_TIME UNLIMITED), you may set EXPTIME as NULL.
Please note that, the way we move the expiration date to a later time is not supported by Oracle, so it's at your own risk.
Sometimes, the expiration notice might be very annoying to users, you may consider to totally avoid ORA-28002 password expiration notice.