ORACLE 查看用户密码修改时间

ORACLE 查看用户密码修改时间

1。当前数据库版本为
Java代码
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 

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$表
Java代码
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> 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。查看某一用户的上一次密码修改时间
Java代码
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 

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密码修改时间已经改变

Java代码
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