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

advertisements

_____________________________________________________________________________________________________________________

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

_____________________________________________________________________________________________________________________

Website Stats