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!
Recipie #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:
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.