Skip to content
Home » Oracle » USER$, How and Why

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

  • 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.

  • SPARE4
  • The user's encrypted password.

  • SPARE6
  • The user's last successful login timestamp in UTC zone.

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

2 thoughts on “USER$, How and Why”

Leave a Reply

Your email address will not be published. Required fields are marked *