TRIGGER TO GET SERVERERROR ON DATABASE

Lets create a trigger that will log all the errors in the database:

It is better to create this trigger under SYS or DBA user's so that you don't have to grant all permission if you have to grant permissions separately.

--------------------------------------------------------
--  DDL for Trigger AFTER_ERROR
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER "AFTER_ERROR"
    AFTER SERVERERROR ON DATABASE
DECLARE
    pragma autonomous_transaction;
    id NUMBER;
    sql_text ORA_NAME_LIST_T;
    v_stmt CLOB;
    n NUMBER;
BEGIN
    SELECT oraerror_seq.nextval INTO id FROM dual; --- Seq no's
    --
    n := ora_sql_txt(sql_text);
    --
    IF n >= 1
    THEN
        FOR i IN 1..n LOOP
            v_stmt := v_stmt || sql_text(i);
        END LOOP;
    END IF;
    --
    FOR n IN 1..ora_server_error_depth
    LOOP
       IF ( ora_server_error(n) in (  '6512','25228','25254')
          AND ora_login_User in ('SYSMAN','DBSNMP') )
       THEN
           -- ignore this error
           NULL;
       ELSE
           INSERT INTO oraerror VALUES (id, sysdate, ora_login_user, ora_client_ip_address, ora_server_error(n), ora_server_error_msg(n),v_stmt);
           COMMIT;
       END IF;
    END LOOP;

/*  You can un comment this part to make this table auto clean on every insert. This will automatically cleanup the data in the table which is older than  30 days (you can change as per your requirement )
Delete from oraerror where log_date <=sysdate -30;
    commit; */
 --
END after_error;
/
ALTER TRIGGER "AFTER_ERROR" ENABLE;





Now create a seq needed for id generation :

--------------------------------------------------------
--  DDL for Sequence ORAERROR_SEQ
--------------------------------------------------------

   CREATE SEQUENCE  "ORAERROR_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1040 CACHE 20 NOORDER  NOCYCLE ;

Table to capture err :


 CREATE TABLE "ORAERROR"
   (    "ID" NUMBER,
    "LOG_DATE" DATE,
    "LOG_USR" VARCHAR2(30 BYTE),
    "TERMINAL" VARCHAR2(50 BYTE),
    "ERR_NR" NUMBER(10,0),
    "ERR_MSG" VARCHAR2(4000 BYTE),
    "STMT" CLOB
   )  TABLESPACE ) ;