ORA-28000 the account is locked

The Problem
There are many reasons why you might get the ORA-28000 the account is locked error message, ranging from too many attempts at logging on with an incorrect password to a password which has expired and subsequently been automatically locked. In Oracle 10g the default password security mechanisms which were included in the default profile (every user is assigned the default profile unless explicitly changed during or after user creation) were more relaxed when compared with the 11g implementation. In 11g there are must more restrictive measures in place to lock out accounts.
View the Status of User Accounts
To see the status of user accounts it’s best to take a look at the DBA_USERS view:
SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS ORDER BY USERNAME;
Lock and Unlock Oracle User Accounts
You can easily unlock an Oracle user account by using the following statement:
ALTER USER username ACCOUNT UNLOCK;
If you want to expire and lock a user account you can use the following statement:
ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;
You may find that once you have unlocked the user account and the ORA-28000: the account is locked error message goes away that you face a new error message: ORA-28002: the password will expire within x days. This is quite common if the default profiles have not been changed after an upgrade to Oracle 11g.
I have written more about the ORA-28002 error message in another article so have a read up of the details there. Also, here are the commands that I used when upgrading my DEV databases from 10.2.0.4 to 11.2.0.3:
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited; alter profile default limit PASSWORD_LIFE_TIME unlimited; alter profile default limit PASSWORD_GRACE_TIME unlimited;
This helped prevent an administration nightmare on DEV with user accounts becoming locked all of the time. However, please bear in mind what kind of data you have on your environment and whether or not this lack of password security will breach any security requirements that you should enforcing.
So, now that you have unlocked the user account you should have resolved the ORA-28000 error message and can move on with your day-to-day DBA activities…Until the next time!
Like it, share it...
Category: Database Config
when I got the error 2800 and I typed:
ALTER USER username ACCOUNT UNLOCK;
to fixe the problem I got another error 0640 that say : not connected.
any idea how to fix it?
thanks
Hi,
Which user are you logged on as when attempting the alter user account unlock statement?
Is there another error which follows the 0640 error? Can you paste the exact error and the exact steps you took to reproduce it?
Thanks,
Rob
Hi,
I am having two issues as the moment:
ORA-28000 & ORA-12560. I am not even able to connect as sysdba to unlock the account? Appreciate your guidance..
Thanks
Hi,
Which user are you trying to log in as when you get the ORA-28000?
Have you see my other article on the ORA-12560 error message?
http://www.ora00600.com/wordpress/scripts/databaseconfig/tns-12560/
Let me know if that helps, and I can try to help you if it doesn’t.
Cheers,
Rob
Thanks man!! you saved my day…
You’re welcome, glad to help 🙂
ORA-28000: the account is locked error getting frequently. Cause: The user has entered wrong password consequently for maximum number of times specified by the user’s profile parameter FAILED_LOGIN_ATTEMPTS, or the database administrator has locked the account
https://www.youtube.com/watch?v=qq3MiwlrwL4
Like the idea with the video, but 4 minutes…? The video could be about 20 seconds long.