Accounts unused for X days

Here's a script that shows accounts and number of days since last use. It assumes session auditing is enabled.

------------------------------------------------------------------------
--
-- File: audit_unused_accounts.sql
--
-- Description: Check for open accounts that haven't been used in X days.
--
-- Parameters: 1 - Number of days of inactivity for which to check (input).
--
------------------------------------------------------------------------

column status format a16

SELECT RPAD(da.username, 12) "Username", 
       TRUNC(SYSDATE - MAX(da.timestamp)) "Days Inactive",
       LPAD(du.account_status, 16) "Status",
       LPAD(TO_CHAR(MAX(da.timestamp), 'DD-MON-YYYY'), 16) "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 != '
 
  '       -- 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

 

For example, to show accounts which haven't been used for at least 30 days:

SQL> @audit_unused_accounts 30

It produces results like this:

Username         Days Inactive Status           Last Login
---------------- ------------- ---------------- ----------------
ALPHA                       34             OPEN      17-MAY-2006
BETA                        89             OPEN      23-MAR-2006
GAMMA                       97             OPEN      15-MAR-2006
DELTA                      118             OPEN      22-FEB-2006
EPSILON                    118   EXPIRED(GRACE)      22-FEB-2006
PHI                        126             OPEN      15-FEB-2006
ETA                        134             OPEN      07-FEB-2006