SYS.USER$ table in Oracle

last password change time, last locked, last expired, creation time, failed logon.
Oracle internal table SYS.USER$ has got many interesting columns for DBAs. This article describes some of them.

Internal table SYS.USER$ keeps both users and roles. The table is basic table that enlist database users and is referenced by almost all other dictionary views.

DESC sys.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


some of the columns have got following meaning:

NAME – name for user or role
TYPE# – 0 for role or 1 for user
CTIME – the date of creation
PTIME – the date the password was last changed
EXPTIME – the date the password has last expired
LTIME – the date the resource was last locked
LCOUNT – number of failed logon


In addition DBA usually looking for last logon that can be found in SYS.AUD$ table.