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

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.