Is there a SQL-based way to identify user accounts that haven't logged in for a long time (to find inactive accounts), without enabling full database auditing?

Q: Is there a SQL-based way to identify user accounts that haven't logged in for a long time (to find inactive accounts), without enabling full database auditing?

For database-authenticated users, FND_USER_API.Get_Last_Activity(user_identity) returns that user's last activity date directly — joining the user table against it (and against DBA_USERS to scope to real database accounts) and filtering for a last-activity date older than a cutoff (or NULL, for accounts that have never logged in) surfaces inactive accounts without needing DB auditing enabled. Keep in mind this specific function only reflects database-authenticated sessions; accounts authenticating through SSO/an external identity provider won't be accurately reflected by this same query.

Answer

For database-authenticated users, FND_USER_API.Get_Last_Activity(user_identity) returns that user's last activity date directly — joining the user table against it (and against DBA_USERS to scope to real database accounts) and filtering for a last-activity date older than a cutoff (or NULL, for accounts that have never logged in) surfaces inactive accounts without needing DB auditing enabled. Keep in mind this specific function only reflects database-authenticated sessions; accounts authenticating through SSO/an external identity provider won't be accurately reflected by this same query.

← Browse Security and access questions