Check the last password change time, last login, expiry date and created date of user in Oracle

Check the user last time of password change

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT PTIME FROM SYS.USER$ WHERE NAME = 'user_name';
Example:
SQL> SELECT PTIME FROM SYS.USER$ where name = 'C##SUNNY';
PTIME
---------
05-JAN-21
SQL> alter user C##SUNNY identified by sunny;
User altered.
SQL> SELECT PTIME FROM SYS.USER$ where name = 'C##SUNNY';
PTIME
---------
07-JAN-21

Check Created date of user in Oracle

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
SELECT CREATED FROM DBA_USERS WHERE USERNAME = 'user_name';
Example:
SQL> SELECT CREATED FROM DBA_USERS WHERE USERNAME = 'C##SUNNY';
CREATED
--------------------
05-JAN-2021 10:25:10

Check the last login date of the user in Oracle

SELECT LAST_LOGIN FROM DBA_USERS WHERE USERNAME = 'user_name';
Example:
SQL> SELECT LAST_LOGIN FROM DBA_USERS WHERE USERNAME = 'C##SUNNY';
LAST_LOGIN
---------------------------------------------------------------------------
05-JAN-21 04.01.33.000000000 PM +05:30

Check the Expiry date for the password of User in Oracle

SELECT EXPIRY_DATE FROM DBA_USERS WHERE USERNAME = 'user_name';
Example:
SQL> SELECT EXPIRY_DATE FROM DBA_USERS WHERE USERNAME = 'C##SUNNY';
EXPIRY_DATE
--------------------


Note: Empty means unlimited set in profile for PASSWORD_LIFE_TIME to UNLIMITED.