There could be a lot of reasons why an Oracle account can be locked. When an account is locked and the users is asking for our help, following could be the possible cause of account lock:

  • A user with appropriate privileges locks an account manually (aka ALTER USER user_name ACCOUNT LOCK)
  • Password parameters from profile
    • User exceeds FAILED_LOGIN_ATTEMPTS
    • PASSWORD_LIFE_TIME is exceeded, or both that and PASSWORD_GRACE_TIME is exceeded.

Are there any other places we should see, or any other ways an account could get locked?

3 Answers 3


Provided audit trail is turned on, then I prefer to use the following to help track down login failures (which is usually the cause of locked accounts):

select * from dba_audit_trail where returncode in (1017, 28000) order by timestamp desc;

returncode is the ORA- error that would be returned from the database: 1017 is "invalid usercode or password" and 28000 is "account is locked".

This view provides valuable information such as the time the login failures occurred, the computer where the error originated from, as well as the OS user.

If you do not have audit_trail turned on, which you can verify with:

show parameter audit_trail;

then obviously, the above query will not help with past login failures, but you may want to consider turning it on to help with future failures. Also, keep in mind that this change requires a bounce of the database to go into effect.

You can refer to this document in order to set this environment variable to a value that is appropriate to your environment: http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams017.htm

You would set this parameter as follows:

alter system set audit_trail='DB' scope=spfile;

Keep in mind that if you do turn on audit_trail, consider periodically clean out older records out of the audit table or the table can grow to be pretty unwieldy. There is an informative blog that talks about that here: http://www.oradba.ch/2011/05/database-audit-and-audit-trail-purging/

As for another possible cause:

If you are running in a distributed environment (multiple databases connected by database links), then proxy database links (null user/password) could be your problem since such database links attempt to login to the destination database with the credentials that are passed from the source database.

If the credentials are different between the source and the destination, ORA-1017 is returned which counts against the login attempts on the destination DB (provided the usercode was the same, and just the passwords are different).

In addition, if the source DB user is logged in via a proxy user, then proxy DB links will ALWAYS fail with ORA-1017 regardless of whether or not the passwords match.


The CONNECT privilege can be revoked


is another way a user's account can be blocked. It's explained well here.


In some cases when there is some objects assigned to a user and that account needs to be removed from database later but you do not want to remove the objects associated to that user account. In this you can lock the user(You can unlock the user later if you want).

For details you can watch the video: https://www.youtube.com/watch?v=nI5B8AHzjUo

  • I dont think the Op wants to know how they can do it. Commented Feb 7 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.