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

前几天在给客户培训过程中，有一客户问到能否查看某一用户的密码修改时间，这样可以减少很多不必要的纠纷，大致研究了一下。

1。当前数据库版本为

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

2。查看user$表

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

3。查看某一用户的上一次密码修改时间

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select PTIME from user$ where name='TEST';

PTIME

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

2009-06-15 14:25:31

SQL> !date

Mon Jun 15 18:20:58 EDT 2009

4.修改test用户密码，可以看到test密码修改时间已经改变

SQL> alter user test identified by test;

User altered.

SQL> select PTIME from user$ where name='TEST';

PTIME

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

2009-06-15 18:21:34

SQL> !date

Mon Jun 15 18:21:44 EDT 2009