How to solve ORA-28000 the account is locked

We often come across the error ORA-28000 the account is locked in day to day activities. This can happened with developers and DBA both.

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 username , profile from dba_users where username='&1';
-----  ------
SCOTT TECH_PW

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

SQL> select RESOURCE_NAME,resource_type,LIMIT from dba_profiles where PROFILE='TECH_PW' ;
RESOURCE_NAME RESOURCE LIMIT
-----------    -------  -----
COMPOSITE_LIMIT KERNEL DEFAULT
SESSIONS_PER_USER KERNEL DEFAULT
CPU_PER_SESSION KERNEL DEFAULT
CPU_PER_CALL KERNEL DEFAULT
LOGICAL_READS_PER_SESSION KERNEL DEFAULT
LOGICAL_READS_PER_CALL KERNEL DEFAULT
IDLE_TIME KERNEL DEFAULT
CONNECT_TIME KERNEL DEFAULT
PRIVATE_SGA KERNEL DEFAULT
FAILED_LOGIN_ATTEMPTS PASSWORD 5
PASSWORD_LIFE_TIME PASSWORD 90
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD 10
PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT
PASSWORD_LOCK_TIME PASSWORD DEFAULT
PASSWORD_GRACE_TIME PASSWORD DEFAULT

Default values can be found by querying the default profile
SQL> select RESOURCE_NAME,resource_type,LIMIT from dba_profiles where PROFILE='DEFAULT';
RESOURCE_NAME RESOURCE LIMIT
-------------  -------   ------
COMPOSITE_LIMIT KERNEL UNLIMITED
SESSIONS_PER_USER KERNEL UNLIMITED
CPU_PER_SESSION KERNEL UNLIMITED
CPU_PER_CALL KERNEL UNLIMITED
LOGICAL_READS_PER_SESSION KERNEL UNLIMITED
LOGICAL_READS_PER_CALL KERNEL UNLIMITED
IDLE_TIME KERNEL UNLIMITED
CONNECT_TIME KERNEL UNLIMITED
PRIVATE_SGA KERNEL UNLIMITED
FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
PASSWORD_LIFE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_TIME PASSWORD UNLIMITED
PASSWORD_REUSE_MAX PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION PASSWORD NULL
PASSWORD_LOCK_TIME PASSWORD UNLIMITED
PASSWORD_GRACE_TIME PASSWORD UNLIMITED

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

In case this is not happened purposely , we can solve the problem using the below approach accordingly

(1) 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.

(2) If the reason for error is unknown and Till the issue is resolved, we can create another profile with unlimited FAILED_LOGIN_ATTEMPTS

SQL> CREATE PROFILE TECH_TMP LIMIT
FAILED_LOGIN_ATTEMPTS UNLIMITED
;

And Assign it to the user which is experiencing the issue

alter user scott profile TECH_TMP;
alter user scott account unlock;

Once the cause is established and we know the source from where invalid password is coming, we can change user profile to previous one and dropped the newly created profile

alter user scott profile TECH_PW;
drop profile TECH_TMP;

Hope you like this post on ORA-28000 the account is locked and it will help in your day to day activities. Please do provide feedback on it

Related Articles

ORA-01017: invalid username/password; logon denied
How to login as user without changing the password in Oracle database
Create User in Oracle
How to create Users and Roles in Oracle database 12c
ORA-00904
ORA-28002
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4003.htm