How to solve ORA-28000 the account is locked

Last updated on March 22nd, 2019 at 05:38 pm

We often come across the error ORA-28000 the account is locked.

Reasons

This can happen due to multiple reasons

a. Oracle DBA has purposely locked the account

alter user <username> account lock;

select status from dba_users where username='&1';

b. Wrong password has been attempted many times resulting in locking. The number of FAILED_LOGIN_ATTEMPTS  can be found using the below query

select profile from dba_users where username='&1';

select * from dba_profiles
where profile='&1' and resource_name='FAILED_LOGIN_ATTEMPTS';

We can also found where all failed login attempt happened by enabling auditing

audit session whenever not successful;

select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode
from dba_audit_trail
where returncode > 0

 

Solution

Unlock the account using below command

alter user <username> account unlock;

Before executing above query  make sure reason for the error is sorted out.Otherwise again the error will happen

Related Articles

ORA-01017: invalid username/password; logon denied Tips

How to login as user without changing the password in Oracle database

How to Create User in Oracle and Assign privileges

How to create Users and Roles in Oracle database 12c