Skip to content

How to Resolve ORA-28001: the password has expired

ORA-28001

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
ERROR:
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.

A preventive action is to make PASSWORD_LIFE_TIME into UNLIMITED in the profile, you may have a look.

Solutions to ORA-28001

There're several ways to solve ORA-28001.

  1. Change Password
  2. The formal way to solve it is to change the password.

  3. Defer Expiration
  4. An unofficial way to solve it is to revert the expiration.

  5. Set Original Password
  6. If you want to set the original password, you can try this way.

1. Change Password

Of course, user can change the password immediately at connect-time.

Changing password for hr
New password:
Retype new password:
Password changed
Connected.

A privileged user can do it too.

SQL> conn / as sysdba
Connected.
SQL> alter user hr identified by hr;

User altered.

I know what you're looking for, just keep reading the post.

2. Defer Expiration

An unsupported way is to postpone the expiration to a latter time and go back to the normal status.

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 go back to the normal status. There will be no more expiration message.

To have a never expired password (PASSWORD_LIFE_TIME UNLIMITED), you should set EXPTIME to 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.

3. 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, at least no direct syntax supports that, we have to set the original password for our user.

Here comes a question, where to find the original password? For 10g and 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
Connected.
SQL> set long 4000;
SQL> select dbms_metadata.get_ddl('USER','HR') stmt from dual;

STMT
--------------------------------------------------------------------------------

   CREATE USER "HR" IDENTIFIED BY VALUES 'S:E129CFF697C9B08E613D3B22B13DC1124B80
F9346C79FAECF8426B7F3580;T:3716DFD59DAF47A9DA01E6EAE75FC3A86D4911C6FD494F5517FE1
4E19F89E781BF193E27338CE1D8F3BEE184B1B22DD1B5C3A6A355E7EB7118E53C30B5CE60E246916
2F618D658D761207BDF81523871'
      DEFAULT TABLESPACE "EXAMPLE"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE

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';

User altered.

Now the user's password is unexpired, just like nothing happened.

SQL> conn hr/hr;
Connected.

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.

10 thoughts on “How to Resolve ORA-28001: the password has expired”

  1. C:>sqlplus /nolog
    SQL> connect / as SYSDBA
    SQL> select * from dba_profiles;
    SQL> alter profile default limit password_life_time unlimited;
    SQL> alter user identified by ;
    SQL> commit;
    SQL> exit;

    1. You can use normal terminal of your system friend. If you on windows, type Windows+R and search CMD, and follow the steps he has said. For Alter user command use:
      ALTER USER Username IDENTIFIED BY “new_password” REPLACE “old_password”;
      If you on MAC, search terminal from applications thats it.

Leave a Reply

Your email address will not be published. Required fields are marked *