SYS.USER$ table in Oracle – last password change time, last locked, last expired, creation ...

Datetime:2016-08-23 02:23:12          Topic: Oracle           Share

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# – 1 for role or 0 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
SELECT NAME, type#, ctime, ptime, exptime, ltime, lcount 
  FROM sys.user$
WHERE NAME IN ('SYS', 'SYSTEM', 'PUBLIC', 'DBA', 'SCOTT')  
ORDER BY NAME;

NAME    TYPE#  CTIME                PTIME                EXPTIME              LTIME                LCOUNT 
------- ------ -------------------- -------------------- -------------------- -------------------- ------
DBA     0      08.12.2011 18:46:07                                                                 0
PUBLIC  0      08.12.2011 18:46:07                                                                 0
SCOTT   1      16.01.2014 02:03:51  16.01.2014 02:03:51  12.05.2014 07:32:37  11.12.2015 05:22:56  0
SYS     1      08.12.2011 18:46:07  09.02.2016 09:51:03                                            0
SYSTEM  1      08.12.2011 18:46:07  05.10.2014 14:38:07  11.03.2016 06:58:36                       4

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

Have a fun ��

Tomasz





About List