Oracle 23c New Features for DBA'sHi
How to track the password change for a user in Oracle?
A) Oracle only tracks the date that the password will expire based on when it was latest changed. So by looking at the DBA_USERS.EXPIRY_DATE and subtracting PASSWORD_LIFE_TIME you can determine when password was last changed. The last password change time can also directly be seen from the PTIME column in dictionary table USER$ (on which DBA_USERS view is based).
But If you have PASSWORD_REUSE_TIME and/or PASSWORD_REUSE_MAX set in a profile assigned to a user account then you can reference dictionary table USER_HISTORY$ for when the password was changed for this account. This will maintain any password which still falls with in the PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX limits.
Must Run this Query after connecting by Sys user
SELECT user$.NAME, user$.PASSWORD, user$.ptime, user_history$.password_date
FROM SYS.user_history$, SYS.user$
WHERE user_history$.user# = user$.user#
NAME PTIME PASSWORD_Changed
------------------- --------- -----------------
Test_AWR_USER 20-JUN-12 04-JUN-12
Test_AWR_USER 20-JUN-12 30-MAY-12
Test_AWR_USER 20-JUN-12 20-JUN-12
Thanks
Geek DBA
Follow Me!!!