How to Find Last Successful Login Date in Oracle

November 18, 2020
()

Find Last Successful Login Date in Oracle

Oracle12c new features

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.

Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

<strong>Hello and welcome to DBsGuru,</strong>DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training. Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.Thanks for the visits!<strong>Share Learn Grow!</strong>

4 Comments

Leave a Reply

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