DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How can i get Oracle-User's Last Login Information ?

  1. #1
    Join Date
    Feb 2001
    Location
    Atlanta, USA
    Posts
    131

    How can i get Oracle-User's Last Login Information ?

    Hi buddies,

    I have a question to you.

    How can i get Oracle-User's Last Login Information ?

    For example, Say, i want to list-out the users, who have not connected/logged in to the database for last 3 months.
    How can i get the last login information?

    Kindly note that i do not want to use any kind of triggers to update the login-info in a table, while login.

    Any other idea ?

    Thanks and regards
    Anandharaj.A
    Anandharaj
    a_anandharaj@yahoo.com

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Don't want any triggers? Then enable auditing in the database....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Try this...........

    -- Need to input 'AUDIT ALL' command for this to work

    col database NEW_VALUE _database
    set termout off
    set echo off
    select name database from v$database;
    set sqlp ''
    @DATE
    set termout on
    col logoff format a19
    col logon_time format a18

    -- break on user_name
    ttitle 'Oracle (' _database ') last 90 days database usage as at ' _date skip 2
    select i.real_name,
    to_char(timestamp, 'DD MON YYYY hh24:mi') logon_time,
    to_char(logoff_time,'DD MON YYYY hh24:mi') logoff
    from dba_audit_session d,
    intravue_system.intravue_users i
    where d.username like ('%_%')
    AND (timestamp > (sysdate - 90))
    and i.username = d.username
    order by logon_time,d.username,timestamp,logoff_time;
    ttitle off

  4. #4
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Oh yeah, you'll need this as it's called from the above script as @date

    /* Sql to set date into a variable _date */
    col today NEW_VALUE _date
    set termout off
    select to_char(sysdate, 'fmMonth DD, YYYY') today from dual;
    set termout on

  5. #5
    Join Date
    Feb 2001
    Location
    Atlanta, USA
    Posts
    131
    Hi Horace,

    Thank you very much for your reply.

    I try to run the given query, but system prompts the error saying that
    "intravue_system.intravue_users" table not found.

    Appreceiate if you would explain about "intravue_system" table.

    thanks and regards
    Anandharaj.A
    Anandharaj
    a_anandharaj@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width