How To Track Password Changed Dates Of An Oracle User?

Password change history of an Oracle user can be checked from the SYS tables USER$ and USER_HISTORY$. 

SQL> Select name,password_date ” Password Changed date” from sys.user$ A ,sys.user_history$ B where A.user# = B.user# and A.name=’TEST_USER’ order by password_date;

NAME                     Password Changed date
——————– ————————–
TEST_USER                Jun 19 2010 09:06:27
TEST_USER                Jun 29 2010 05:05:16
TEST_USER                Oct 27 2010 09:56:54
TEST_USER                Jan 20 2011 21:47:55
TEST_USER                Sep 04 2011 22:06:16
TEST_USER                Sep 04 2011 22:19:35
TEST_USER                Sep 04 2011 22:48:57

Note : USER_HISTORY$ table gets updated only if the user is assigned a profile with password reuse limit (i.e. PASSWORD_REUSE_TIME should not be UNLIMITED)
Also, We can query the PTIME column of SYS.USER$ to check when the password was last changed.

SQL> select PTIME ” Password was last changed on” from sys.user$ where NAME=’TEST_USER’;

Password was last changed
————————–
Sep 04 2011 22:48:57

Article by : Anju