Friday, August 15, 2008

Finding inactive users in Oracle

# enable session audit

sqlplus '/ as sysdba'
alter system set audit_trail=db scope=spfile;
shutdown immediate;
startup
audit session;

# after a few months of normal database operation find the users who have not logged in.

select username, created from dba_users where account_status = 'OPEN' and created < sysdate -90 and not exists
(select distinct userid from aud$ where userid = username and LOGOFF$TIME > sysdate -90)
order by username;