What do the columns in SYS.USER$ represent?

In the SYS.USER$ view, what do the CTIME, PTIME, and LTIME columns represent?

Where can I find Oracle's documentation for this and other system views?

Test cases below show:

• CTIME is the date the user was created.

• LTIME is the date the user was last locked. (Note that it doesn't get NULLed when you unlock the user).

• PTIME is the date the password was last changed.

• LCOUNT is the number of failed logins.

Here's the test code:

SQL> create user philtest identified by philtest;

User created.

SQL> alter session set nls_date_format='HH24:MI:SS DD/MM/YYYY';

Session altered.

SQL> select ctime,ltime,ptime from user$ where name = 'PHILTEST';

CTIME               LTIME               PTIME
------------------- ------------------- -------------------
14:21:51 26/04/2012                     14:21:51 26/04/2012

SQL> alter user philtest account lock;

User altered.

SQL> select ctime,ltime,ptime from user$ where name = 'PHILTEST';

CTIME               LTIME               PTIME
------------------- ------------------- -------------------
14:21:51 26/04/2012 14:23:15 26/04/2012 14:21:51 26/04/2012

SQL> alter user philtest identified by foofoo;

User altered.

SQL> select ctime,ltime,ptime from user$ where name = 'PHILTEST';

CTIME               LTIME               PTIME
------------------- ------------------- -------------------
14:21:51 26/04/2012 14:23:15 26/04/2012 14:27:47 26/04/2012

SQL>

This isn't documented in the Oracle docs, as it's an internal data dictionary view. However, the DBA_%/ALL_%/USER_% views that sit on top of the SYS.% views are documented here.

I often find that doing a DESCribe on one of the DBA_% views gives a good indication of what the underlying columns represent. The DBA_% views are often commented too.

StackExchange Database Administrators Q#17513, answer score: 7