Oracle Schema User Locked in trans.log ORA-28000

951167- ORA-28000: the account is locked

Problem
You cannot log on to the Oracle database and the program returns the message:

ORA-28000: the account is locked



Reason and Prerequisites

ORA-28000 is usually triggered because someone has previously attempted to log on several times using an incorrect password, which causes the logon to fail and returns ORA-01017. The number of allowed logon attempts is defined by the resource FAILED_LOGIN_ATTEMPTS in the profile. You can determine the current value of this parameter using the following query:

SELECT LIMIT FROM DBA_PROFILES
WHERE
PROFILE = 'DEFAULT' AND
RESOURCE_NAME = 'FAILED_LOGIN_ATTEMPTS';

Until Oracle 10.1, this parameter had the default value UNLIMITED, so that no ORA-28000 messages would normally occur. As of Oracle 10.2, this standard value has been set to 10.

A further option in the case of a locked user is an explicit lock as follows:

ALTER USER ACCOUNT LOCK;


Solution

To prevent ORA-28000 errors, and avoid similar problems in future, be aware of the following:

If you use Oracle 9i or earlier, do not manually restrict the authorizations of the default profile (also refer to Note 700548 (16)).
If FAILED_LOGIN_ATTEMPTS is not set to UNLIMITED, you can adjust it as follows:

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
This is also possible for Oracle 10. 1 and earlier and Oracle 10.2 and later.

If a user is already locked, you can unlock it as follows:

ALTER USER ACCOUNT UNLOCK;
If the ORA-28000 error occurred due to a large number of failed logon attempts, you must check why these logon attempts were unsuccessful and returned the ORA-01017 error. The cause is often an incorrect configuration of the OPS$ mechanism. In this case, refer to Note 400241.