How can I check user login times in 11g

Posted on 2011-10-13
Last Modified: 2013-01-19
Hi guys, which view I can use to check a particular user's login and logoff times.
Question by:nirvairghuman
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    If you don't have auditing turned on I believe you will need a system trigger to track this.

    There might be other options that I do not know about.

    Author Comment

    Our auditing is on,
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    If you are auditing the session then you should be able to query the audit tables.

    On mobile and cannot search for examples but there should be examples in the online docs on querying the audit trail.
    LVL 73

    Accepted Solution

    eslect * from dba_audit_trail where action_name in ('LOGON','LOGOFF') and username = 'YOURUSER'
    LVL 47

    Assisted Solution

    I have used this simple approach (in the SYS schema):

    1. Create the table to capture the LOGIN and LOGOUT timings.

    DROP TABLE ICW_logonaudittable;

    CREATE TABLE ICW_logonaudittable
      event       VARCHAR2(10),
      sid         NUMBER,
      serial#     NUMBER,
      timestamp   DATE,
      username    VARCHAR2(30),
      osuserid    VARCHAR2(30),
      machinename VARCHAR2(64),
      ip          VARCHAR2(20)

    2. Create LOGON trigger at DATABASE level.
      machinename VARCHAR2(64);
      osuserid    VARCHAR2(30);
      v_sid       NUMBER(10);
      v_serial    NUMBER(10);
      v_ip        VARCHAR2(20);
      CURSOR c1 IS
        SELECT sid, serial#, osuser, machine, sys_context('userenv','ip_address')
          FROM v$session WHERE audsid = userenv('sessionid');
      OPEN c1;
      FETCH c1 INTO v_sid, v_serial, osuserid, machinename, v_ip;
      INSERT INTO ICW_logonaudittable VALUES ( 'LOGON', v_sid, v_serial, sysdate,
          user, osuserid, machinename, v_ip);
      CLOSE c1;

    3. Check the result:

    set linesize 1000
    set pagesize 1000
    set trimspool on
    set heading on
    set underline '-'
    column event format A9
    column sid   format 99999
    column serial# format 99999
    column username format A20 wrap
    column osuserid format A20 wrap
    column machinename format A20 wrap
    column timestamp format A30 wrap
    select event, sid, serial#, username, osuserid,
           to_char(timestamp,'dd-mon-yyyy hh24:mi:ss') as TIMESTAMP
    from ICW_logonaudittable;
    LVL 3

    Expert Comment

          They have already enable AUDIT then why we need to maintain system level trigger and user defined audit table?
          I accept system audit table wont maintain IP address. but you have used that. I didnt think IP will be useful in all the cases.

          All the application will maintain application level audit that is another context.
    LVL 73

    Assisted Solution

    >>> why we need to maintain system level trigger

    there isn't a "need",  it's simply an alternate approach.

    however in 11g the comment_text of dba_audit_trail may contain tns connect info that includes client ip/name

    for example....

    Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=

    it's not reliable for all sessions, but many

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Oracle SQL select approach 8 49
    display data from previous rows 5 58
    Salary Amount Format 13 52
    update set column values in oracle 3 32
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now