Which system view or v$veiw to use for get list of username not login to database for past 90 days?
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