Oracle user password expired / User does not exist in system

2 min read 05-10-2024

Oracle user password expired / User does not exist in system

Oracle User Headaches: Password Expired or User Gone?

Encountering the dreaded "ORA-28000: the account is locked" error in Oracle can be incredibly frustrating. This message often comes paired with either "password expired" or "user does not exist in system" and leaves you feeling stranded. But fear not! This article will guide you through understanding these errors, troubleshooting them, and ultimately getting your Oracle user back in action.

Scenario: The User is Locked Out

Imagine you're working on a critical project, accessing your Oracle database to perform crucial tasks. Suddenly, you're met with the infamous "ORA-28000" error. You try logging in again, but the same message pops up. Your heart sinks - your user account seems to be locked!

Here's a typical example of the error you might see in your SQL*Plus session:

SQL> CONNECT username/password
ERROR:
ORA-28000: the account is locked
ORA-28001: password expired

Or, you might get this instead:

SQL> CONNECT username/password
ERROR:
ORA-28000: the account is locked
ORA-00942: table or view does not exist

Deciphering the Error Messages

Password Expired: The most common culprit is a simple password expiration. Oracle databases often have policies that force users to change their passwords periodically. When this happens, the account gets locked, preventing unauthorized access.

User Does Not Exist: This error usually means the user account has been deleted, possibly accidentally or due to an administrative decision.

Troubleshooting Steps

Check Password Expiration:
- SQL*Plus: Use the ALTER USER command to check the last password change date:
```
SQL> ALTER USER username IDENTIFIED BY VALUES 'password';
```
  This command will display the last password change date. If it's outdated, your password may have expired.
- Oracle Enterprise Manager: Navigate to the "Security" section, find your user, and check the "Last Password Change" field.
Reset the Password:
- SQL*Plus: Use the ALTER USER command to reset the password. You'll need administrative privileges for this:
```
SQL> ALTER USER username IDENTIFIED BY "new_password"; 
```
- Oracle Enterprise Manager: Locate your user, click "Edit", and then click "Set Password". Enter your new password and confirm.
Verify User Existence:
- SQL*Plus: Execute the following query to check if the user exists:
```
SQL> SELECT * FROM dba_users WHERE username = 'username';
```
  If no results are returned, the user has been deleted.
Recreate the User Account:
- SQL*Plus: Use the CREATE USER command to recreate the account:
```
SQL> CREATE USER username IDENTIFIED BY "new_password" DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;
```
  You'll need to assign appropriate roles and permissions to the new user.
- Oracle Enterprise Manager: Navigate to the "Security" section, click "Create User", and fill in the required details.

Additional Tips

Password Policies: Familiarize yourself with your database's password policies to avoid future expiration issues.
Database Administrators: If you're unsure about password policies or face difficulties with user accounts, consult your database administrators for assistance.
Backup and Recovery: Regularly backup your database to avoid data loss in case of accidental user deletion.

Conclusion

The "ORA-28000" error can be a real headache, but by understanding the causes and following the outlined troubleshooting steps, you can quickly resolve the issue and regain access to your Oracle database. Remember, proactive measures such as password management and regular backups can prevent future headaches and keep your data safe.