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.
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