How to list all users who have not logged in the last n number of days in 11g ORACLE database

I had an audit request where i was ask to list all users who have not logged in the database for more than 120 days.

In 12c it has become really easy. We can just check the table DBA_USERS and can read LAST_LOGIN column as below.

Check users who have not logged in database from

N number of days in 12c oracle database:

col username   format a15 col last_login format a25 select   username,   TO_CHAR((last_login ), 'DD-MON-YYYY') as "LAST LOGIN" from   dba_users where TO_CHAR((last_login ), 'DD-MON-YYYY') <= SYSDATE - &1 -- enter the number of days you want for your business need -- order by as your needs order by 1,2 DESC;

In 11g its a little tricky we have to join a few tables to come up with the same results as below. 

Check users who have not logged in database from

N number of days in 11g oracle database:

set linesize  250  set pagesize 250  column status format a16 column username format a16 --column "DAYS INACTIVE" format a30  column "LAST LOGIN" format a16 SELECT RPAD(da.username, 12) as Username,         TRUNC(SYSDATE - MAX(da.timestamp)) as "DAYS INACTIVE",        LPAD(du.account_status, 16) as Status,        LPAD(TO_CHAR(MAX(da.timestamp), 'DD-MON-YYYY'), 16) as "LAST LOGIN" FROM dba_users du, dba_audit_session da  WHERE da.action_name LIKE 'LOG%' AND da.username NOT IN ('SYS','SYSTEM') -- itemize accounts to exclude AND du.profile != 'APPUSER'   -- or profiles to exclude     AND du.username = da.username AND du.account_status = 'OPEN'          -- could look at just OPEN if desired GROUP BY da.username, du.account_status HAVING MAX(da.timestamp) <= SYSDATE - &1 ORDER BY 2,1 DESC;

That is it you have a list you need