前几天在给客户培训过程中，有一客户问到能否查看某一用户的密码修改时间，这样可以减少很多不必要的纠纷，大致研究了一下。
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
-------------------
-06-15 14:25:31
SQL> !date
Mon Jun 15 18:20:58 EDT
4.修改test用户密码，可以看到test密码修改时间已经改变
SQL> alter user test identified by test;
User altered.
SQL> select PTIME from user$ where name='TEST';
PTIME
-------------------
-06-15 18:21:34
SQL> !date
Mon Jun 15 18:21:44 EDT
