Looking at DBA_AUDIT_TRAIL

The view DBA_AUDIT_TRAIL displays all standard audit trail entries in an Oracle database. In my case, I was using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0.

The following SQL can be used to look at entries in this view.

SELECT os_username,
       username,
       terminal,
       To_char(timestamp, 'YYYY-MM-DD HH24:MI:SS') AS tstamp,
       returncode,
       action_name
FROM   dba_audit_trail
order by tstamp asc;

Sample output:

OS_USERNAME  USERNAME  TERMINAL     TSTAMP   RETURNCODE ACTION_NAME

------------ ------------ ------------ ------------------- ---------- ----------------------------

oracle     DBSNMP  ~       2014-10-17 18:32:10 1017 LOGON

root     RLBUSY  unknown      2014-10-17 18:32:15    0 LOGOFF

abc082     IMPREP  MACH-42    2014-10-17 18:32:35    0 LOGOFF

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGON

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGON

oracle     DBSNMP  ~       2014-10-17 18:32:57    0 LOGON

oracle     DBSNMP  ~       2014-10-17 18:32:57    0 LOGOFF

FRED       DUMMY  ~       2014-10-17 18:33:00 1017 LOGON

oracle     DBSNMP  ~       2014-10-17 18:33:29 1017 LOGON

oracle     DBSNMP  unknown      2014-10-17 18:33:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:33:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:34:36    0 LOGON

oracle     DBSNMP  unknown      2014-10-17 18:34:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  ~       2014-10-17 18:34:39 1017 LOGON

oracle     DBSNMP  ~       2014-10-17 18:34:53    0 LOGOFF

oracle     DBSNMP  ~       2014-10-17 18:34:53    0 LOGON

root     RLBUSY  unknown      2014-10-17 18:35:00    0 LOGON

The RETURNCODE column is the number associated with the ORA-????? error code that was returned to the client computer:

RETURNCODE=0 indicates success

RETURNCODE=1017 indicates bad password

RETURNCODE=28000 indicates account is locked out

For confirmation of the meaning of the returncode value, execute the following code:

SQL> execute dbms_output.put_line(sqlerrm(-1017)) ;

and you should see:

ORA-01017: invalid username/password; logon denied

or from the O/S prompt:

[oracle@host ~]$ oerr ora 01017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:

See also

Oracle Support Note "Master Note For Oracle Database Auditing (Doc ID 1299033.1)"

Keywords: oracle audit trail failed login