How to Check Failed Login Attempts in Oracle Database

 Introduction

Monitoring failed login attempts in Oracle Database is very important for security. As a DBA, you should always be aware of unauthorized login attempts to protect sensitive data.

1. Using the DBA_AUDIT_TRAIL View

If auditing is enabled, you can check failed login attempts with:

SELECT username, os_username, userhost, terminal, timestamp, returncode 

FROM dba_audit_trail 

WHERE action_name = 'LOGON' 

AND returncode != 0 

ORDER BY timestamp DESC;

👉 returncode != 0 means the login attempt failed.

2. Using the DBA_AUDIT_SESSION View

SELECT username, os_username, userhost, returncode, timestamp 

FROM dba_audit_session 

WHERE returncode != 0 

ORDER BY timestamp DESC;

3. Common Return Codes

1017 → Invalid username or password.

28000 → Account locked.

Conclusion

Checking failed login attempts in Oracle is essential for database security. By regularly monitoring the audit views, you can detect suspicious activity early and take preventive action.