oracle stale user accounts

would there be any tell tale signs in a an oracle database about "stale accounts", i.e. those that could be locked and epxired? I am not sure i there is a last login type flag, or whether that is the most appropriate marker, as perhaps some accounts dont "login" like a normal user would?
Who is Participating?
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
i think only after you enable audit - audit sessions etc then you will have a history of who is connecting, when etc, whether the remaining can be locked and the other information.

Is the auditing enabled ?
Geert GConnect With a Mentor Oracle dbaCommented:
audit .. or a login trigger which stores the last login attempt in a separate table
pma111Author Commented:
but by default there is no last login stamp? is that correct
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

pma111Author Commented:
can you have accounts that are neccesary but may not "login" as such, can you provide examples of these kinds of account?
Geert GOracle dbaCommented:
well... sys is very necessary
but usually only dba's login with that when they setup the database

system also

that's the main 2, the rest is optional
but that again depends on what you are using

here is a list of the oracle default users
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
You can query DBA_USERS to find out any accounts that are actually set to expired and/or locked (a column called ACCOUNT_STATUS).

No way short of a login trigger that immediately comes to mind to track last login.

In 12c, they've added last_login to dba_users, but prior to that, you have to track it yourself.
Naveen KumarProduction Manager / Application Support ManagerCommented:
also unless you do a deep dig into all the user account details in your database, it would not be easy to say which all accounts are required and which are not required.

To start with, probably you can extract the information from dba_users and then start the investigation from there.

As mentioned already, do not delete/drop any internal/system accounts which are required for the database.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.