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.
Comments