USER last login details in oracle database

This page will showing how to check out the last login details into oracle database there are several view like SYS.AUD$ , SYS.AUDIT_ACTIONS with the help of that you can easily find out the last login session information.

SQL > SELECT TO_CHAR(TIMESTAMP#,'MM/DD/YY HH:MI:SS') TIMESTAMP,USERID, b.NAME, ACTION FROM SYS.AUD$ a, SYS.AUDIT_ACTIONS b
WHERE a.ACTION# = b.ACTION
and b.name='LOGON'
and userid in
('&User_id')
ORDER BY TIMESTAMP# DESC;

And also you can use the dba_audit_session view to check out the logon and logoff time of any user.

SQL > select OS_USERNAME,action_name,USERNAME,to_char(timestamp, 'DD MON YYYY hh24:mi') logon_time,
to_char(logoff_time,'DD MON YYYY hh24:mi') logoff
from dba_audit_session where username ='&user'
AND (timestamp > (sysdate – 80))
order by logon_time,username,timestamp,logoff_time;

You can find out the DBA Audit Trail data

col USERNAME for a15
col OS_USERNAME for a15
col USERHOST for a15
col ACTION_NAME for a15
set pages 200
set lines 100

SQL > alter session set nls_date_format='DD-MON-YY HH24:MI:SS';

SQL > select USERNAME,OS_USERNAME,USERHOST,TIMESTAMP,ACTION_NAME,
LOGOFF_TIME from dba_audit_trail where username='&USERNAME' order by USERHOST;

Please share this Blog with your colleagues or friends. Your suggestions and feedback are very helpful for everyone who come to this site and learn it from oracleocpworld.com.
Please comment here for your any query related to above post. You can email me on : oracleocpworld@gmail.com.