This content has been marked as final. Show 3 replies
"You would enable auditing in the database and audit connects. This will record the login/logout activity into the audit trail. The audit trail is either a database table OR the system event log (OS specific as to where that is). If you are using the database table, creating a flat file is trivial (simple query and spool from sqlplus)."
The auditing information looks comprehensible to me. You do need to change your predeicate though, this
action_name in ('LOGOG','LOGOFF')
action_name in ('LOGON','LOGOFF','LOGOFF BY CLEANUP')
shipon_97 wrote:It would be more convenient to query dba_audit_session - probably using subquery factoring with materialization, then join two copies to itself by sessionid, copy one with the LOGON action, and copy 2 where the action_name != 'LOGON'.
In Oracle 11g, I cannot get user's login/logon information properly . when a user logon and then logout from his session then I got the below information using the below specified sql query :
select username,userhost,timestamp,action_name,logoff_time from dba_audit_trail where timestamp between to_date('03-FEB-2013 09:00:00','DD-MON-YYYY hh24:mi:ss') and to_date('03-FEB-2013 23:00:00','DD-MON-YYYY hh24:mi:ss') and action_name in ('LOGOG','LOGOFF') and username='TEST' order by timestamp desc
It's interesting to see from the link to AskTom (ca. 2004, at least) a simple repeated connect user/pw from sql *plus could give you one logon record with lots of logoff records, though - I hadn't noticed that before.