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