SYS.USER_ASTATUS_MAP provides a list of account status bit and their definitions. Let's see its content. In which, ASTATUS stands for "Account STATUS".
SQL> show user
USER is "SYS"
SQL> select * from sys.user_astatus_map order by 1;
STATUS# STATUS
---------- --------------------------------
0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
8 LOCKED
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED
9 rows selected.
For an OPEN account, its status bit can be 0 or 16. What we don't see in the list is 16, the bit means that the account is OPEN and using the default password.
In other words, if an user is using the default password, then it will be listed in DBA_USERS_WITH_DEFPWD view and its status code must be 16 or greater.
Let's see some other unlisted bits and their statuses.
- 16
- 17 = 16 + 1
- 18 = 16 + 2
- 20 = 16 + 4
- 24 = 16 + 8
- 25 = 16 + 9
OPEN, with using the default password.
EXPIRED, with using the default password. The user will see error ORA-28001.
EXPIRED(GRACE), with using the default password. The user will see error ORA-28002.
LOCKED in PASSWORD_LOCK_TIME, with using the default password. The user will see error ORA-28000.
LOCKED, with using the default password. The user will see error ORA-28000.
EXPIRED & LOCKED, with using the default password.
In summary, different status bits may have the same status with or without using the default password.
- 0 and 16 are OPEN
- 1 and 17 are EXPIRED
- 2 and 18 are EXPIRED(GRACE)
- 8 and 24 are LOCKED
- 9 and 25 are EXPIRED & LOCKED
The current STATUS# of a specific user can be looked upon SYS.USER$.
SQL> show user;
USER is "SYS"
SQL> select astatus from sys.user$ where name = 'HR';
ASTATUS
----------
0
Whereas STATUS of a specific user can be lookup upon data dictionaries DBA_USERS or USER_USERS.
SQL> show user
USER is "HR"
SQL> select account_status from user_users;
ACCOUNT_STATUS
--------------------------------
OPEN
The account is healthy.