Wednesday, 30 November 2016

ORA-28000: the account is locked

Unlock User and set FAILED_LOGIN_ATTEMPTS to unlimited.






View Profile Assigned to a User:

SQL> SELECT PROFILE FROM DBA_USERS
WHERE USERNAME='SCOTT';

  PROFILE
 ----------
  DEFAULT

View Limits Set for the Profile:

SQL> SELECT RESOURCE_NAME, LIMIT FROM DBA_PROFILES
WHERE PROFILE='DEFAULT';

 RESOURCE_NAME  LIMIT   
------------------------    -------------       
COMPOSITE_LIMIT                UNLIMITED       
SESSIONS_PER_USER              UNLIMITED       
CPU_PER_SESSION               UNLIMITED       
CPU_PER_CALL                   UNLIMITED       
LOGICAL_READS_PER_SESSION     UNLIMITED       
LOGICAL_READS_PER_CALL        UNLIMITED       
IDLE_TIME                      UNLIMITED       
CONNECT_TIME                   UNLIMITED       
PRIVATE_SGA                    UNLIMITED       
FAILED_LOGIN_ATTEMPTS         10       
PASSWORD_LIFE_TIME            UNLIMITED       
PASSWORD_REUSE_TIME            UNLIMITED       
PASSWORD_REUSE_MAX            UNLIMITED       
PASSWORD_VERIFY_  FUNCTION     NULL       
PASSWORD_LOCK_TIME             UNLIMITED       
PASSWORD_GRACE_TIME            UNLIMITED       
16 rows selected.

Alter FAILED_LOGIN_ATTEMPTS Parameter:

From the user's profile we can see that the FAILED_LOGIN_ATTEMPTS parameter is set to a value of 10. Now we run below command to make it UNLIMITED.

SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
Profile altered.

Unlock the Account:

Finally we will unlock the account by running below command.

SQL> ALTER USER SCOTT ACCOUNT UNLOCK;