What do the columns in SYS.USER$ represent?

Question

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?

Answer 1

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.

Answer 2

It is not intended that you use the internal tables like user$ directly. You should query the static ditionary views sys.dba_% instead of. These are well documented in the Reference Manual

Hardcore Oracle freaks can read the comments in the file" $ORACLE_HOME/rdbms/admin/sql.bsq" or browse sites like this one