USER$, How and Why

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, 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#

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.

• NAME

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.

• TYPE#

0 means a role, 1 means a user.

• DATATS#

The default permanent tablespace number of the user. The number refers to SYS.TS$ table.

• TEMPTS#

The default temporary tablespace number of the user. The number refers to SYS.TS$ table.

• CTIME

The user's creation data time.

• PTIME

The user's last password change date time.

• EXPTIME

The user's password expiration data time.

• LTIME

The user's account lock date time. NULL means currently no lock.

• ASTATUS

The user's account status number which is defined in USER_ASTATUS_MAP.

• LCOUNT

The user's failed login attempts.

• DEFSCHCLASS

The user's default schedule class.

• SPARE4

The user's encrypted password.

• SPARE6

The user's last successful login time in UTC zone.

If you know more columns, please leave your comment below.