To solve ORA-28001 and ORA-28002, the best way is to follow the rule to change user's password. However, if you had to continue using the current password for some reason, making password expiration happen later is an option.
An unsupported way is to postpone the expiration to a latter time and go back to the normal status by updating SYS.USER$.
Update USER$
As we talked about SYS.USER$ table in the previous post. Normally, we should not update the table, it's unsupported and risky, you might break the password policy and data integrity.
Let's see how we do it.
SQL> show user
USER is "SYS"
SQL> update user$ set ptime = sysdate, exptime = sysdate + 180, ltime = null, lcount = 0, astatus = 0, spare6 = systimestamp at time zone 'UTC' where name = 'HR';
1 row updated.
SQL> commit;
Commit complete.
A flushing on SGA may be required to clear some data dictionary cache.
SQL> alter system flush shared_pool;
System altered.
In UPDATE statement, we put off the expiration by adding 180 days from now and go back to the normal status by setting ASTATUS to 0. There will be no more expiration message.
Let's see more variations that you should know.
ASTATUS bit
Either 0 or 16 of ASTATUS bit means that the account is OPEN according to the view SYS.USER_ASTATUS_MAP.
Set 0 or 16 ?
For normal situations, you should set ASTATUS to 0. However, if the user is using the default password, you should set ASTATUS to 16.
Default Password ?
How do we know whether the user is using the default password or not? You should look up DBA_USERS_WITH_DEFPWD for sure, if the user is in the list, it's using the default password.
How to Make Password Never Expire?
To have a never expired password, you should set PASSWORD_LIFE_TIME to UNLIMITED after you set a new password for the user or defer the expiration like we said in the above.
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.