I am a heavy user of Oracle database and I have installed the Oracle 11g on my desktop at work. We use it as our primary database and I created several different instances of database users on my local edition of the RDBMS. Recently, I logged into an old DB user that I had created on my desktop after quite some time. It was an older version of our software and so when the Services team reported a bug, I wanted to spin up the instance and try to debug the reason for the error.
When I tried logging into the DB, I kept getting the ORA-28001: The password has expired error. Now, I am familiar with changing password and I fired up a command prompt, logged in as the root. I ran the query below to change the password:
SQL> ALTER USER system_36 IDENTIFIED BY system_36; –system_36 is the existing username and password
When I tried logging in as system_36, it again gave me the same error of ORA-28001. On doing some research I found that by default the password expiry is set to 180 days. The account doesn’t fix itself simply by changing the password on it. I am outlining the solution that worked for me below:
1. Connect to the database with sysdba privileges (sys user).
2. Execute the following script to identify the profile for which you want to set the password life to unlimited from the regular 180 days default setting. SQL> SELECT * FROM dba_profiles;
3. In case you want to apply the behavior to all future users, execute the query below else replace the DEFAULT profile with profile you want to change
SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
4. Now to unlock the user account execute the following query
SQL> ALTER USER system_36 ACCOUNT UNLOCK;
5. Now you can change the password/apply the existing one again using the following query
SQL> ALTER USER system_36 IDENTIFIED BY system_36;
The user information is stored in dba_users table if you want to check and unlock other users on the same server.