查询oracle用户修改密码时间,ORACLE 查看用户密码修改时间

SQL> show user

USER is "SYS"

SQL> create user t1 identified by t1;

create user t1 identified by t1

*

ERROR at line 1:

ORA-01920: user name 'T1' conflicts with another user or role name

SQL> drop user t1 cascade;

User dropped.

SQL> create user t1 identified by t1;

User created.

SQL> grant connect to t1;

Grant succeeded.

SQL> conn t1/t1

Connected.

SQL> conn / as sysdba

Connected.

SQL> desc user$

Name Null? Type

----------------------------------------- -------- ----------------------------

USER# NOT NULL NUMBER

NAME NOT NULL VARCHAR2(30)

TYPE# NOT NULL NUMBER

PASSWORD VARCHAR2(30)

DATATS# NOT NULL NUMBER

TEMPTS# NOT NULL NUMBER

CTIME NOT NULL DATE

PTIME DATE //就是这个字段代表了用户最近一次密码更改的时间

EXPTIME DATE

LTIME DATE

RESOURCE$ NOT NULL NUMBER

AUDIT$ VARCHAR2(38)

DEFROLE NOT NULL NUMBER

DEFGRP# NUMBER

DEFGRP_SEQ# NUMBER

ASTATUS NOT NULL NUMBER

LCOUNT NOT NULL NUMBER

DEFSCHCLASS VARCHAR2(30)

EXT_USERNAME VARCHAR2(4000)

SPARE1 NUMBER

SPARE2 NUMBER

SPARE3 NUMBER

SPARE4 VARCHAR2(1000)

SPARE5 VARCHAR2(1000)

SPARE6 DATE

SQL> select ptime from user$ where name='T1';

PTIME

---------------

20-NOV-09

//换一下nls_date_format 这样能够更精确的看到当前时间

SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';

Session altered.

SQL> select ptime from user$ where name='T1';

PTIME

--------------------------

2009-NOV-20 19:55:20

SQL> alter user t1 identified by t2;

User altered.

SQL> conn t1/t1

ERROR:

ORA-01017:invalid username/password;logon denied

Warning: You are no longer connected to ORACLE.

SQL> conn t1/t2

Connected.

SQL> conn / as sysdba

Connected.

SQL> select ptime from user$ where name='T1';

PTIME

---------------

20-NOV-09

SQL> alter session set nls_date_format='YYYY-MON-DD HH24:MI:SS';

Session altered.

SQL> select ptime from user$ where name='T1';

PTIME

--------------------------

2009-NOV-20 19:57:32

//可以看到 这里ptime和上面相比已经发生了变化