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?
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 NULL
ed 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 DESC
ribe 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
SYS.DBA_USERS
first, but it does not expose PTIME
or LCOUNT
, both of which are very useful for monitoring certain user behavior.
May 7, 2012 at 15:01