How to Find Failed Login Attempts in Oracle Database

Introduction

Monitoring failed login attempts is critical for database security. Oracle provides audit views to help DBAs detect unauthorized access.

Steps:

1. Connect to the Database as SYSDBA

sqlplus / as sysdba

2. Query Failed Logins Using DBA_AUDIT_SESSION

SELECT username, userhost, returncode, timestamp 

FROM dba_audit_session 

WHERE returncode != 0 

ORDER BY timestamp DESC;

3. Query Using DBA_AUDIT_TRAIL (Optional)

SELECT username, action_name, returncode, timestamp 

FROM dba_audit_trail 

WHERE action_name = 'LOGON' 

AND returncode != 0 

ORDER BY timestamp DESC;

4. Common Return Codes

1017 → Invalid username or password

28000 → Account locked

Conclusion

Regularly monitoring failed logins helps protect your Oracle Database from unauthorized access and security threats.