Oracle: Understanding the table user$

Table user$:

SQL> desc user$ Name Null? Type ----------------------------------------- -------- ---------------------------- USER# NOT NULL NUMBER NAME NOT NULL VARCHAR2(30) TYPE# NOT NULL NUMBER PASSWORD VARCHAR2(30) DATATS# NOT NULL NUMBER TEMPTS# NOT NULL NUMBER CTIME NOT NULL DATE PTIME DATE EXPTIME DATE LTIME DATE RESOURCE$ NOT NULL NUMBER AUDIT$ VARCHAR2(38) DEFROLE NOT NULL NUMBER DEFGRP# NUMBER DEFGRP_SEQ# NUMBER ASTATUS NOT NULL NUMBER LCOUNT NOT NULL NUMBER DEFSCHCLASS VARCHAR2(30) EXT_USERNAME VARCHAR2(4000) SPARE1 NUMBER SPARE2 NUMBER SPARE3 NUMBER SPARE4 VARCHAR2(1000) SPARE5 VARCHAR2(1000) SPARE6 DATE

Explanation of some columns:

• CTIME is the date the user was created.
• LTIME is the date the user was last locked. (Note that it doesn't get NULLed when you unlock the user).
• PTIME is the date the password was last changed.
• LCOUNT is the number of failed logins.

Table user_astatus_map:

SQL> desc sys.user_astatus_map Name Null? Type ----------------------------------------- -------- ---------------------------- STATUS# NOT NULL NUMBER STATUS NOT NULL VARCHAR2(32)

In this table is a list of all statuses. Therefore you can join those two tables.

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 9 rows selected.