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 ) ;
