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?
asked May 7, 2012 at 10:48
-
1I could not find this information using Google or on Oracle's support website. Unfortunately, much of Oracle's documentation is poorly indexed.– Nick ChammasMay 7, 2012 at 10:51
Add a comment
|
2 Answers
Test cases below show:
CTIMEis the date the user was created.LTIMEis the date the user was last locked. (Note that it doesn't getNULLed when you unlock the user).PTIMEis the date the password was last changed.LCOUNTis 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.
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
-
I checked out
SYS.DBA_USERSfirst, but it does not exposePTIMEorLCOUNT, both of which are very useful for monitoring certain user behavior. May 7, 2012 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 May 7, 2012 at 15:35