Monitor / Audit Oracle Database Errors

It might not be a useful thing on a development database, but you might want to track 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!

Recipe #1 - Create Trigger to Monitor Database Errors

First, we have to create a table where the errors are stored (make sure to use a user 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 which 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.