I have some users who are logging in with a wrong password and their accounts get locked.

Now I need to find the source of the failed login attempt. The machine, username etc.

Is it possible to find this?

I did try

select ntimestamp#, userid, userhost, spare1, comment$text from sys.aud$ where returncode=1017 order by 1;

and also,

select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode
from dba_audit_trail 
where returncode > 0

Both the above queries show up results but I'm not sure if the results are the ones which actually gets locked.

Server - RHEL DB - Oracle 12c

  • Perhaps you might get lucky looking in listener.log or enable db audit for access.
    – Raj
    Commented Dec 18, 2015 at 13:59
  • listener would show high volume of hits.
    – tesla747
    Commented Dec 19, 2015 at 7:14

1 Answer 1


You are on the right way. Column RETURNCODE in DBA_AUDIT_TRAIL or RETURN_CODE in UNIFIED_AUDIT_TRAIL view.


This view is populated only in an Oracle Database where unified auditing is not enabled. When unified auditing is enabled in Oracle Database, the audit records are populated in the new audit trail and can be viewed from UNIFIED_AUDIT_TRAIL.

Oracle documentation is always a very good source of information.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.