We sometime might see accounts getting locked in the Oracle Database. This may be because of failed login attempt due to the wrong password. So we often look at How to check failed login attempts in Oracle.
we can find the failed login attempt in the database using the below query. You need to provide the username if you are looking for a particular user or just leave blank, it will show for all the users
We have another view DBA_AUDIT_TRAIL, which can be also used to detect invalid login attempts. If you are interested in a particular user, give the username as input
You can below query also to summarize all the invalid attempts in the database
We can also find the number of attempt before the account get locked using the below query
These all queries fetch the data from SYS.AUD$ tables which are enabled by setting audit_trail=DB in init.ora
If don’t have audit enabled then and you don’t want to set it, then you can set the event to generate the trace whenever failed login attempt happened
You can also set trigger to write failed login information in alert log