Find accounts unused for days
                                                 Last update (2008-03-01 22:43:09)
                                                                                                                   Date added (2008-02-27 15:10:13)

Summary
Here's a script that shows accounts and number of days since last use. It assumes session auditing is enabled.
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;
The alternative if you don't want to enable auditing is to create a login trigger that stores login information in some table
But....
Logon triggers always have a little risk; if something goes wrong (and things can go wrong) logins could get blocked; for example, if the logon table is unavailable, or a record is locked etc. You must be at the edge to disable them if something go wrong.
Reviews
Filters
Search