如何在Oracle中开发after serverror触发器？

我正在尝试将数据库中的所有错误记录到表中.因此,当用户sys i编写以下代码时：

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;

@H_502_3@但是,当我强制尝试从不存在的表中选择一个错误时,它不会在表中记录错误.

@H_502_3@有没有办法检查触发器是否会触发？此外,我尝试创建一个测试表来插入那里,但它也不起作用,即使将触发器定义为自治事务并在触发器内提交.

@H_502_3@谢谢,
华金

@H_404_13@ 不要查询v $sql;使用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,captured_sql)
   VALUES
   (systimestamp,stmt);
   commit;
 END log_server_errors;
 /

@H_502_3@然后：

sql> select * from c;

@H_502_3@这会产生：

select * from c
              *
ERROR at line 1:
ORA-00942: table or view does not exist

@H_502_3@现在可以查询：

select * from servererror_log;

@H_502_3@生产：

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中开发after serverror触发器？

猜你在找的Oracle相关文章