Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
I could not find this information using Google or on Oracle's support website. Unfortunately, much of Oracle's documentation is poorly indexed. –  Nick Chammas May 7 '12 at 10:51
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
add comment

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

share|improve this answer
    
I checked out SYS.DBA_USERS first, but it does not expose PTIME or LCOUNT, both of which are very useful for monitoring certain user behavior. –  Nick Chammas May 7 '12 at 15:01
1  
@Nick seeing as they are intentionally undocumented you shouldn't rely on them existing in future versions, nor assume that their behaviour is always what it seems to be –  Jack Douglas May 7 '12 at 15:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.