How to Interpret the ACCOUNT_STATUS Column in DBA_USERS

The DBA_USERS.ACCOUNT_STATUS can have the following values :

select * from user_astatus_map;

STATUS# STATUS
0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
8 LOCKED
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED

These values are directly related to two features 'Account Locking' and 'Password Aging and Expiration'




Account Locking - LOCKED/LOCKED(TIMED)

An account can be locked by a DBA or is locked automatically after a number of failed login attempts. 
When a PASSWORD_LOCK_TIME is defined, the account unlocks automatically after the set time: this is indicated by LOCKED(TIMED). The LOCKED(TIMED) value is what you always see if the account was locked due to the number of failed logins > FAILED_LOGIN_ATTEMPTS.

You only see LOCKED if the account is explicitly locked during create or alter user/role.

You may expect that when the account is automatically locked and PASSWORD_LOCK_TIME is set to unlimited, the account would appear as just LOCKED. This is however not the case: it still shows as LOCKED(TIMED). However the account never unlocks automatically because of the unlimited PASSWORD_LOCK_TIME (infinity).

To add to the confusion, at some point it was decided that even if the account is locked automatically, we should not set it to LOCKED(TIMED) if the PASSWORD_LOCK_TIME is unlimited, since in that case the account would never automatically unlock, this would give up on a crucial piece of information, namely if the account was locked manually or automatically, this change was introduced in 11.2.0.1. However this change caused a regression in Bug 9693615 causing the lock_date to be NULL in dba_users in case the account was locked automatically, the fix to this bug backed out the change again and now we have the LOCKED(TIMED) for automatically locked accounts back with this fix

So a DBA will know that when the ACCOUNT_STATUS is LOCKED(TIMED) that the lock was a result of a failed login attempt, even if the lock will not expire. In 11.2.0.1 (without the fix to Bug 9693615) you can verify if the account was locked automatically if the lock_date in dba_users is null.




Password Expiration - EXPIRED/EXPIRED(GRACE)

A password can be set to expire, with or without a grace period:
●● When a password expires and no grace is defined, the password is set at EXPIRED, meaning that the user is prompted for a new password upon the next login attempt.
●● When a grace is defined, during the grace period, a warning is issued, and the ACCOUNT_STATUS is set to EXPIRED(GRACE).




Password Expiration and Account Locking are two separate features:

●● An account cannot be locked by exceeding the expire or subsequent grace time.
●● An account that is neither expired nor locked appears as OPEN (STATUS# 0),

Since account locking (based on failed login attempts) and expiration (based on not changing password) are basically unrelated.

In addition to the EXPIRED (1,2) and LOCKED (4,8) bits being set, you can have combinations of both with STATUS# 5,6,9 and 10: internally the STATUS# are added for the combinations of expired and locked.
For example : 'EXPIRED' + 'LOCKED' = 1 + 8 = 9 = 'EXPIRED & LOCKED'