Get the last login date for all users in Oracle 11g

user_2011684 0
Viewed 27362

We have Oracle 11g so the following query didn't work.

select USERNAME,LAST_LOGIN from dba_users

Then I searched and people suggested multiple ways and i got confused. What is the correct way to get the last login date for all users in oracle 11g?

select username, logon_time
from v$session

or

SELECT
    username,
    timestamp
FROM
    sys.dba_audit_session

or

select username, timestamp
from dba_audit_Trail
oracle11g oracle sql
3 Answers

DBA_AUDIT_TRAIL table records all the actions performed by the user based on level of auditing enforced and it also records login and logoff time of every user which can be identified from action column.

 SELECT  MAX(TIMESTAMP), A.USERNAME FROM DBA_AUDIT_TRAIL A WHERE ACTION_NAME = 'LOGON'
    GROUP BY USERNAME ORDER BY 1 DESC;
user_109082740

This should work:

select username, last_login
from sys.dba_users;

You only need to ask your dba to grant you access to that table.

user_53997190

Please use below query, this is the right way to check the user and their last login date

select username, max(logon_time) as logon_time from v$session where username is not null
group by username;

Demo

enter image description here

user_44736150
Related