Friday , December 6 2024

Check When Password Was Last Changed in Oracle

The most recent password change date for users in the Oracle database is in the ptime field in the sys.user $ table. With a query like the following, we can find out when a user last changed the password.

in sys.user$ table;

NAME: user or role name
TYPE #: 0 means role, 1 means user
CTIME: date created
PTIME: last password change date
EXPTIME: the date the password was last expired
LTIME: the latest lock date
LCOUNT: how many times he/she entered his/her password incorrectly

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

2 comments

  1. Thanks it helps!..

    • If granting access to SYS.USER$ (for non-DBAs) is an issue, below query is an alternative:

      SELECT U.USERNAME, (SELECT U.EXPIRY_DATE – P.LIMIT FROM DBA_PROFILES P
      WHERE P.PROFILE = U.PROFILE AND P.RESOURCE_NAME=’PASSWORD_LIFE_TIME’) PWD_CHG_DT
      FROM DBA_USERS U WHERE U.USERNAME=’&USER_NAME’;

Leave a Reply

Your email address will not be published. Required fields are marked *