To find the last successful login date in oracle is now easy to retrieve from supporting version 12c onwards i.e this is a new feature of Oracle 12c.

The question comes to mind why we require the user’s last successful login date, it may be due to the clean-up of users for audit purposes or on-demand requests of the application team/business or users are no more in support, etc. There are many ways to find the last successful login of users like dictionary views, audit enables on LOGON or LOGOFF or enables trigger on LOGON, etc. Here we will demonstrate dictionary view DBA_USERS applicable to 12c onward versions and DBA_AUDIT_TRAIL if an audit is enabled on the session by access (LOGON / LOGOFF) and it will be helpful to find before the 12c version if an audit is enabled.

Below is SQL command to find last successful login date using DBA_USERS (12c onward version). Click here for sample output.

COL USERNAME FOR A25
COL ACCOUNT_STATUS FOR A19
COL EXPIRY_DATE FOR A13
COL LAST_LOGIN FOR A41
	
select USERNAME,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE,LAST_LOGIN from dba_users 
order by LAST_LOGIN DESC,USERNAME;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

Below is SQL command to find the last successful login date using DBA_AUDIT_TRAIL if an audit is enabled on the session by access (LOGON / LOGOFF) and it will be helpful to find before the 12c version. Click here for sample output.

SET LINES 333 PAGES 222
COL TIMESTAMP FOR A31 	
COL USERNAME FOR A15

SELECT  MAX(to_char(TIMESTAMP, 'hh24:mi:ss dd/mm/yy')) LAST_LOGIN, USERNAME FROM DBA_AUDIT_TRAIL  
WHERE ACTION_NAME = 'LOGON' GROUP BY USERNAME ORDER BY LAST_LOGIN DESC;
prompt$$$$$$$**Welcome to DBsGuru!**Share Learn Grow**$$$$$$$

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

• WordPress
• LinkedIn
• Facebook
• Twitter
• Instagram
• Mail