-
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
-
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?
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|