用触发器捕捉数据库所有报错sql

author:zy

公司数据库因存储挂了，短时间无法恢复，需要通过读库、归档恢复到一个新的数据库。从而恢复业务

由于读库不全，从读库导过来的对象（表、sequence、procedure..）不全。致应用日志报“对象不存在”、数据库“library cache lock”、等等的大量错误及等待事件。

而开发也没法较快定位程序，不知道哪条sql报错（高并发环境下），所以暂不知道哪些对象缺失。

当时简短分析后，决定采用数据库触发器来捕获问题sql,触发类型为 SERVERERROR。

最终的代码原型为：

--首先创建日志记录表，记录报错sql、主机等等一些信息

create table UTIL_ERROR_LOG

(

  username VARCHAR2(50),

  host     VARCHAR2(50),

  os_user  VARCHAR2(50),

  ip       VARCHAR2(50),

  type     VARCHAR2(50),

  time     DATE default sysdate,

  sql      VARCHAR2(4000)

)

trigger --监控oracle错误号，这里的“对象不存在”对应的是ora-0942.

    程序比较简单，但功能比较实用

create or replace trigger t_error_sql   after SERVERERROR  on database when (user not in('SYS') and ora_sysevent not in ('COMMENT')) declare   n        int; --n个的意思   sql_text ora_name_list_t;   --v_sql    clob; ORA_SQL_TXT读取到的sql含“/*中文*/”会乱码，clob装载这些乱码会报错   v_sql   varchar(32767); --有可能读取到乱码   v_error varchar2(30); begin   IF (IS_SERVERERROR(0942)) THEN     v_error := '0942';   end if;   if v_error is not null then     n := ORA_SQL_TXT(sql_text);     --sql_text(i)过了51就会报错，受不了这么大的值     if n < 51 then       for i in 1 .. n loop         --dbms_output.put_line(sql_text(i));         v_sql := v_sql || sql_text(i);       end loop;     end if;     insert /*+append*/     into util_error_log     values       (user,        sys_context('userenv', 'host'),        sys_context('userenv', 'os_user'),        sys_context('userenv', 'ip_address'),        v_error,        sysdate,        v_sql); --也有补获不到sql的情况   end if;   /*exception --调试信息   when others then     dbms_output.put_line(n);     raise_application_error(-20000,                             sqlerrm || ' ' ||                             dbms_utility.format_error_backtrace() || ' N:' || n);*/ end t_error_sql;

结：

Oracle trigger十分灵活，特别在生产上，不应随便创建。在经过严格的测试后，布置上去来实现一些事件前后的一些处理。

常用触发器实现的功能：

1. ddl语句监控
2. 特殊核心表不能删保护
3. 报错语句的监控
4. 登陆

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30109892/viewspace-1993180/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30109892/viewspace-1993180/