SYS.USER$ is a table which stores all user's password information. Generally, the table should be used only by Oracle internal system and not DBA. If you intend to use it, for example, unexpiring an user's password, you should be very careful.
The columns in the table are:
SQL> desc user$
Name Null? Type
----------------------------------------- -------- ----------------------------
USER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(128)
TYPE# NOT NULL NUMBER
PASSWORD VARCHAR2(4000)
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(128)
EXT_USERNAME VARCHAR2(4000)
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 NUMBER
SPARE4 VARCHAR2(1000)
SPARE5 VARCHAR2(1000)
SPARE6 DATE
SPARE7 VARCHAR2(4000)
SPARE8 VARCHAR2(4000)
SPARE9 NUMBER
SPARE10 NUMBER
SPARE11 TIMESTAMP(6)
In the table, some column's usage have been known.
- USER#
- NAME
- TYPE#
- DATATS#
- TEMPTS#
- CTIME
- PTIME
- EXPTIME
- LTIME
- ASTATUS
- LCOUNT
- DEFSCHCLASS
- SPARE4
- SPARE6
The unique ID of an user in the database, which reflects to USER_ID in DBA_USERS view. If the TYPE# is 0, USER# becomes ROLE_ID in DBA_ROLES view.
The unique name of an user or a role in the database, which reflects to USERNAME in DBA_USERS view. If the TYPE# is 0, NAME becomes ROLE in DBA_ROLES view.
0 means a role, 1 means a user.
The default permanent tablespace number of the user. The number refers to SYS.TS$ table.
The default temporary tablespace number of the user. The number refers to SYS.TS$ table.
The user's creation data time.
The user's last password change date time.
The user's password expiration data time.
The user's account lock date time. NULL means currently no lock.
The user's account status number which is defined in USER_ASTATUS_MAP.
The user's failed login attempts.
The user's default schedule class.
The user's encrypted password.
The user's last successful login timestamp in UTC zone.
If you know more columns, please leave your comment below.
What about the SPARE5 column? Is that possibly the previous password?
Good thinking! We’ll find out sometime.