Database Administration (MOSC)

MOSC Banner

Which system view or v$veiw to use for get list of username not login to database for past 90 days?

edited Dec 12, 2013 10:29AM in Database Administration (MOSC) 16 commentsAnswered
Hi: 

This question could be tricky because I realized v$session only keep 30 days record in my database, and we didn't enable DB audit, it need upper-manager's approval.

My users want to get a monthly report for database users that have been 'inactive' for past 90 days, or 180 days, then we want to delete these usernames from database.

However, this query give no result-

 select username,to_char(logon_time,'mm-dd-yyyy hh24:mi:ss') from v$session
where type!='BACKGROUND' and logon_time <= sysdate-180 order by logon_time,username;

This is maximum 'last login day' I can get -


  1* select username,to_char(logon_time,'mm-dd-yyyy hh24:mi:ss') from v$session where type!='BACKGROUND' and logon_time <= sysdate-30 order by logon_time,username

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center