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
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
I believe ■TYPE# – 0 for role and 1 for user
Fixed this type 🙂
Thanks
Tomasz
Thanks for post.
Does Lcount specifies lifetime failed login ?
Excelente explicación, me ha servido un mundo.
Muchas gracias.
thank you very much…!!! 🙂
What about TYPE# = 2
Pingback: How To Connect To An Oracle 8i Database As SYSDBA – Programming & Design
Pingback: oracle user$-亿信开发者
“In addition DBA usually looking for last logon that can be found in SYS.AUD$ table”
Last logon can be found in SYS.USER$ (spare6) or DBA_USERS (LAST_LOGIN).
You can check it
select USERNAME,LAST_LOGIN from DBA_USERS where USERNAME = ‘X’
SELECT NAME, spare6 FROM sys.user$ WHERE NAME = ‘X’