This discussion is archived
3 Replies Latest reply: Feb 3, 2013 4:08 AM by Jonathan Lewis RSS

login logout information in oracle11g

shipon_97 Newbie
Currently Being Moderated
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


----
USERNAME, USERHOST, TIMESTAMP, ACTION_NAME, LOGOFF_TIME

"TEST",, 2/3/2013 12:18:09 PM, "LOGOFF",``` 2/3/2013 12:18:09 PM

"TEST",, 2/3/2013 12:17:57 PM, "LOGOFF", 2/3/2013 12:17:57 PM
---

Here in the output, during LOGON ,the output shows same time in the TIMESTAMP as well as LOGOFF_TIME fields . Also the LOGOUT moment, it also shows the same time in TIMESTAMP and LOGOFF_TIME fields . For this reason I cannot find out the proper information of a user's login/logout time .

Would any body help me , how I can find the login/logout information in Oracle11g environemtnt .
  • 1. Re: login logout information in oracle11g
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    "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)."

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1830073957439
  • 2. Re: login logout information in oracle11g
    JohnWatson Guru
    Currently Being Moderated
    The auditing information looks comprehensible to me. You do need to change your predeicate though, this
    action_name in ('LOGOG','LOGOFF')
    should read
    action_name in ('LOGON','LOGOFF','LOGOFF BY CLEANUP')
  • 3. Re: login logout information in oracle11g
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    shipon_97 wrote:
    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 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'.

    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.

    Regards
    Jonathan Lewis

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points