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
