ORA-28000 Account is Locked in Oracle – Unlock User Easily

 ORA-28000: Account is Locked – Fix in Oracle

This error occurs when a user account is locked due to multiple failed login attempts. To resolve the ORA-28000 error, you must log in to the database as a user with administrative privileges (like SYS or SYSTEM) and manually unlock the account. This error typically occurs when a user exceeds the maximum number of failed login attempts defined by their security profile. 

Error

ORA-28000: the account is locked

Causes

• Multiple wrong password attempts
• Profile lock settings

Quick Fix: Unlock the User

Use the following steps in SQL*Plus or a similar command-line tool: 

1.      Connect as SYSDBA:

Sql> sqlplus / as sysdba

2.      Unlock the Account:
Replace username with the actual name of the locked user.

Sql> ALTER USER username ACCOUNT UNLOCK;

3.      Optional: Reset the Password:

If the account was locked because the user forgot their password, you can reset it simultaneously.

Sql> ALTER USER username IDENTIFIED BY new_password ACCOUNT UNLOCK;

 Verify Account Status

You can check if a user is currently locked by querying the dba_users view: 

Sql> SELECT username, account_status

FROM dba_users

WHERE username = 'YOUR_USERNAME';

 Preventing Future Lockouts

If accounts are locking frequently, you may need to adjust the security profile settings: 

·         Check Attempt Limits: Run this to see the current limit for failed attempts.

Sql> SELECT limit FROM dba_profiles

WHERE profile = 'DEFAULT' AND resource_name = 'FAILED_LOGIN_ATTEMPTS';

·         Increase Limits: You can set the limit to UNLIMITED or a higher number.

Sql> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

·         Identify the Source: If an account relocks immediately after being unlocked, a background service or application might be using an old, cached password. Use SELECT username, machine, program FROM v$session; to find active sessions for that user.

Conclusion

Unlock the account and ensure correct credentials.