Oracle Servererror Login Trail.

SQL> CREATE TABLE LOG_FAILED_TRAIL
  2  (
  3    USER_NAME           VARCHAR2(200),
  4    ERROR_MESSAGE   VARCHAR2(300),
  5    LOGON_TIME          DATE                           DEFAULT SYSDATE,
  6    HOST_OR_IP          VARCHAR2(100 BYTE)             DEFAULT SYS_CONTEXT('USERENV', 'IP_ADDRESS')
  7  );
Table created.
SQL> CREATE OR REPLACE TRIGGER LOGON_FAILURES_AUDIT
  2     AFTER SERVERERROR
  3     ON DATABASE
  4    DECLARE
  5    V_DB_USER VARCHAR2(300);
  6    V_ERROR_CODE VARCHAR2(100);
  7    V_ERROR_DESC VARCHAR2(200);
  8    V_HOST_OR_IP VARCHAR2(100);
  9    PROCEDURE SP_ERROR_TRACE IS
 10    BEGIN
 11      IF (IS_SERVERERROR(1017)) THEN
 12      V_ERROR_CODE:='1017';
 13      V_ERROR_DESC:='Invalid username/password.';
 14       ELSIF (IS_SERVERERROR(1005)) THEN
 15      V_ERROR_CODE:='1005';
 16      V_ERROR_DESC:='Null password given.';
 17           ELSIF (IS_SERVERERROR(1004)) THEN
 18      V_ERROR_CODE:='1004';
 19       V_ERROR_DESC:='Default username/password.';
 20           ELSIF (IS_SERVERERROR(1035)) THEN
 21      V_ERROR_CODE:='1035';
 22       V_ERROR_DESC:='ORACLE only available to users with RESTRICTED SESSION privilege.';
 23           ELSIF (IS_SERVERERROR(1045)) THEN
 24      V_ERROR_CODE:='1045';
 25       V_ERROR_DESC:='User lacks CREATE SESSION privilege.';
 26        ELSE
 27      V_ERROR_CODE:='0000';
 28      V_ERROR_DESC:='Invalid Error.';
 29      END IF;
 30    END;
 31
 32  BEGIN
 33      V_DB_USER:= SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY');
 34      V_HOST_OR_IP:=SYS_CONTEXT('USERENV', 'IP_ADDRESS');
 35      SP_ERROR_TRACE;
 36     IF V_ERROR_CODE IS NOT NULL
 37     THEN
 38       BEGIN
 39        INSERT INTO LOG_FAILED_TRAIL (USER_NAME, ERROR_MESSAGE,HOST_OR_IP)
 40        VALUES (V_DB_USER, V_ERROR_CODE||'-'||V_ERROR_DESC,V_HOST_OR_IP);
 41       EXCEPTION
 42              WHEN OTHERS THEN
 43              NULL;
 44       END;
 45     END IF;
 46  END;
 47  /
Trigger created.
SQL> CONN TEST/TEST;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn /as sysdba
Connected.
SQL> SELECT USER_NAME, ERROR_MESSAGE
  2  FROM LOG_FAILED_TRAIL;
USER_NAME  ERROR_MESSAGE
-------------- ----------------------------------------------------------------
TEST    1017-Invalid username/password.
SQL>