Problem

Monitor / Audit Oracle Database Errors

It might not be a useful thing on a development database, but you might want to try having errors in your production system. Got too many people working on the database and want to track down the person who's responsible for the errors, add this trigger!

Solution

Recipe #1 - Create Trigger to Monitor Database Errors

First, we have to create a table where the errors are stored, but make sure it's a use who has global rights on the database:

CREATE TABLE error_log (
  server_error VARCHAR2(100),
  osuser VARCHAR2(30),
  username VARCHAR2(30),
  machine VARCHAR2(64),
  process VARCHAR2(24),
  program VARCHAR2(48),
  stmt VARCHAR2(4000),
  msg VARCHAR2(4000),
  date_created DATE  
)

After we've created the table, we simply add a trigger with is fired by "AFTER SERVERERROR ON DATABASE":

CREATE OR REPLACE
TRIGGER error_log_trigger 
    AFTER SERVERERROR ON DATABASE
DECLARE
    username_  error_log.username%TYPE;
    osuser_    error_log.osuser%TYPE;
    machine_   error_log.machine%TYPE;
    process_   error_log.process%TYPE;
    program_   error_log.program%TYPE;
  stmt_      VARCHAR2(4000);
  msg_       VARCHAR2(4000);
  sql_text_  ora_name_list_t;
BEGIN

  FOR i IN 1..NVL(ora_sql_txt(sql_text_), 0) LOOP  
    stmt_ := SUBSTR(stmt_ || sql_text_(i) ,1,4000);
  END LOOP;

  FOR i IN 1..ora_server_error_depth LOOP
    msg_ := SUBSTR(msg_ || ora_server_error_msg(i) ,1,4000);
  END LOOP;

  SELECT osuser, username, machine, process, program
  INTO   osuser_, username_, machine_, process_, program_
  FROM   sys.v_$session
  WHERE  audsid = USERENV('SESSIONID');

  INSERT INTO error_log VALUES (dbms_standard.server_error(1), osuser_, username_, machine_, process_, program_, stmt_, msg_, SYSDATE);
END;

If you now wait, you'll find all kinds of entries in the error_log table.