-- I added this table and trigger to the database last August. It has 
-- saved my butt numerous times! So simple, and what a big payoff!

CREATE TABLE SYSTEM.ERROR_LOG
(
 TIME_STAMP     DATE,
 USERNAME       VARCHAR2(30 BYTE),
 INSTANCE_NAME  NUMBER,
 DATABASE_NAME  VARCHAR2(50 BYTE),
 ERROR_STACK    VARCHAR2(1000 BYTE)
)
TABLESPACE DEVELOPMENT;


CREATE PUBLIC SYNONYM ERROR_LOG FOR ERROR_LOG;
/



CREATE OR REPLACE TRIGGER SYSTEM.LOGERRORS
AFTER SERVERERROR
ON DATABASE
DECLARE

BEGIN


-- Add the following IF STATEMENT IN FOR 10g SUPPORT:
IF SYS.LOGIN_USER NOT IN ('DBSNMP','SYSMAN') THEN

 INSERT INTO ERROR_LOG
   VALUES (SYSDATE, SYS.LOGIN_USER, SYS.INSTANCE_NUM,
        SYS.DATABASE_NAME,
  DBMS_UTILITY.FORMAT_ERROR_STACK);

 
DELETE FROM ERROR_LOG
     WHERE time_stamp < (SYSDATE - (10));

END IF;

END LogErrors;
/