Oracle 10g, 11g User Password Last Modification / Reset time - sys.user$

In Oracle the password reset/change time is stored in ptime column of sys.user$ table and the user created time is stored in ctime column.
See Example
Initially the CTIME and PTIME column values are same.
SQL>  select name, TO_CHAR(ctime,'DD-MM-YYYY HH:MI') CTIME, TO_CHAR(ptime,'DD-MM-YYYY HH:MI') PTIME
  2  from sys.user$ where name ='SCOTT';

NAME                           CTIME            PTIME
------------------------------ ---------------- ----------------
SCOTT                         31-05-2011 09:09 31-05-2011 09:09

Password Changed for the SCOTT user

SQL> alter user SCOTT identified by welcome1;

User altered.

PTIME changed to current system time.
SQL> select name, TO_CHAR(ctime,'DD-MM-YYYY HH:MI') CTIME, TO_CHAR(ptime,'DD-MM-YYYY HH:MI') PTIME
  2  from sys.user$ where name ='SCOTT';

NAME                           CTIME            PTIME
------------------------------ ---------------- ----------------
SCOTT                         31-05-2011 09:09 22-06-2011 05:43

SQL> select TO_CHAR(sysdate,'DD-MM-YYYY HH:MI') CurrTime from dual;

CURRTIME
----------------
22-06-2011 05:44
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google