ORA-28001 means that your password has been expired according to your profile. Normally, you should change the password, but you can still work around it. Don't be confused with ORA-28000: The account is locked, it's a different account problem.
SQL> conn hr/hr
ORA-28001: the password has expired
While ORA-28001 alerts that the password has expired, ORA-28002 warns that the password will expired soon in grace time.
Solutions to ORA-28001
There're two methods to solve ORA-28001, the first one is to change password as they want. The other is to set the original password for users.
Method 1: Change Password
Of course, user can change the password immediately at connect-time.
Changing password for hr
Retype new password:
A privileged user can do it too.
SQL> conn / as sysdba
SQL> alter user hr identified by hr;
I know what you're looking for, just keep reading the post.
Method 2: Set Original Password
In some cases, changing password can not be done because the password is bound to specific application and even worse, no one remember the original one. So is there any way to unexpire the password? No, no syntax can achieve that, we have to set the original password for our user.
Here comes a question, where to find the original password? For 10g or early versions, just lookup the PASSWORD column in the dictionary view DBA_USERS, which is the encrypted value of password. But for later versions, the column is deprecated and shows nothing.
Now, let's see what we can do to find the original password.
1. Generate the user's DDL.
SQL> conn / as sysdba
SQL> set long 4000;
SQL> select dbms_metadata.get_ddl('USER','HR') stmt from dual;
CREATE USER "HR" IDENTIFIED BY VALUES 'S:E129CFF697C9B08E613D3B22B13DC1124B80
DEFAULT TABLESPACE "EXAMPLE"
TEMPORARY TABLESPACE "TEMP"
2. Set Password by VALUES String
In the output, the string follows IDENTIFIED BY VALUES is the original encrypted password. We can use it to set the password. Just don't forget to remove line breaks in betweens before using it.
SQL> alter USER "HR" IDENTIFIED BY VALUES 'S:E129CFF697C9B08E613D3B22B13DC1124B80F9346C79FAECF8426B7F3580;T:3716DFD59DAF47A9DA01E6EAE75FC3A86D4911C6FD494F5517FE14E19F89E781BF193E27338CE1D8F3BEE184B1B22DD1B5C3A6A355E7EB7118E53C30B5CE60E2469162F618D658D761207BDF81523871';
Now the user's password is unexpired, just like nothing happened.
SQL> conn hr/hr;
Please note that, changing the profile with unlimited PASSWORD_LIFE_TIME for the user can not save ORA-28001 at this moment. But it makes your password unexpired afterwards.