如何在Oracle中开发一个serverror触发器? [英] How to develop an after serverror trigger in Oracle?
问题描述
我正在尝试将数据库中的所有错误记录到表中.因此,作为用户sys,我编写了以下代码:
I'm trying to log all the errors in my database into a table. So as user sys i wrote the following code:
CREATE TABLE servererror_log (
error_datetime TIMESTAMP,
error_user VARCHAR2(30),
db_name VARCHAR2(9),
error_stack VARCHAR2(2000),
captured_sql VARCHAR2(1000));
/
CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE
DECLARE
captured_sql VARCHAR2(1000);
BEGIN
SELECT q.sql_text
INTO captured_sql
FROM gv$sql q, gv$sql_cursor c, gv$session s
WHERE s.audsid = audsid
AND s.prev_sql_addr = q.address
AND q.address = c.parent_handle;
INSERT INTO servererror_log
(error_datetime, error_user, db_name,
error_stack, captured_sql)
VALUES
(systimestamp, sys.login_user, sys.database_name,
dbms_utility.format_error_stack, captured_sql);
END log_server_errors;
但是当我强制执行一个错误(例如尝试从不存在的表中进行选择)时,该错误不会记录在该表中.
But when i force an error like trying to select from a non-existing table it doesn´t log the error in the table.
是否有任何方法可以检查触发器是否触发?另外,我尝试创建一个要插入到其中的测试表,但是即使将触发器定义为自主事务并在触发器内部提交,它也无法正常工作.
Is there any way to check that the trigger fires at all? Also, I tried creating a test table to insert there but it doesn't work either, even if a define the trigger as an autonomous transaction and commit inside the trigger.
谢谢, 华金
推荐答案
不查询v $ sql;使用ora_sql_txt获取语句.
Do not query v$sql; get the statement using ora_sql_txt.
CREATE OR REPLACE TRIGGER log_server_errors
AFTER SERVERERROR
ON DATABASE
DECLARE
sql_text ora_name_list_t;
stmt clob;
n number;
BEGIN
n := ora_sql_txt(sql_text);
if n > 1000 then n:= 1000; end if ;
FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
END LOOP;
INSERT INTO servererror_log
(error_datetime, error_user, db_name,
error_stack, captured_sql)
VALUES
(systimestamp, sys.login_user, sys.database_name,
dbms_utility.format_error_stack, stmt);
commit;
END log_server_errors;
/
然后:
SQL> select * from c;
这将产生:
select * from c
*
ERROR at line 1:
ORA-00942: table or view does not exist
现在可以查询:
select * from servererror_log;
产生:
ERROR_DATETIME
---------------------------------------------------------------------------
ERROR_USER DB_NAME
------------------------------ ---------
ERROR_STACK
--------------------------------------------------------------------------------
CAPTURED_SQL
--------------------------------------------------------------------------------
11-FEB-09 02.55.35.591259 PM
SYS TS.WORLD
ORA-00942: table or view does not exist
select * from c
这篇关于如何在Oracle中开发一个serverror触发器?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!