How to Resolve ORA-28002: the password will expire within 7 days

ORA-28002

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
ERROR:
ORA-28002: the password will expire within 7 days


Connected.

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.

1. Change Password

The formal way to solve it is to change the password.

2. Defer Expiration

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.

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

We can also use ALTER USER to change the password before expiration.

SQL> alter user hr identified by welcome1;

User altered.

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.

SQL> commit;

Commit complete.

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.