Listing inactive database accounts without auditing turned on (Oracle V11)

I sometimes get asked for information on when users last logged into a database so that the application owner can identify inactive accounts. This is especially useful if they are licensed by the number of users.

This is fine if you are on V12:

select username,last_login from dba_users;

USERNAME             LAST_LOGIN
--------             ----------
ANDY                 10-JAN-17 10:53:22

However, V11 doesn't have this column, and unless you have auditing on you can't use the audit trail.

Due to a quirk with the accounts, an account_status will show as OPEN if the user hasn't logged in since the password expired.

So this query:

select a.name,a.ctime,a.ptime,b.account_status,b.profile,c.limit
from sys.user$ a,dba_users b,dba_profiles c
where c.resource_name='PASSWORD_LIFE_TIME'
and a.name=b.username
and b.profile=c.profile
and c.limit != 'UNLIMITED';

will show the last time the password was changed (ptime) and the number of days after which it will expire (limit).

 NAME CTIME     PTIME    ACCOUNT_STATUS     PROFILE            LIMIT
--------------- -------  ---------          --------           ---- 
ANDY 02-FEB-15 02-FEB-15 OPEN         APP_ADM_DEFAULT          40

So this shows that even though the user was created and password set on the 02-FEB-15, and the password expires after 40 days, the account is still OPEN, not LOCKED or EXPIRED.

According to this Oracle Support note -


Why Account Status Is Open When Expiry Date is Old Date in DBA_USERS (Doc ID 291195.1)

unless the user attempts to connect after the password has expired, the account will show as OPEN.

So any account that is in OPEN status and is past the expiry_date for the profile is likely to be unused.