/************创建用于审计的表t_trace************/
SQL> create table t_trace(username varchar2(10),terminal varchar2(10),ip varchar2(20),time date,action varchar2(100),errormsg varchar2(1000));
 
Table created

/***********创建用于审计系统操作出错事件的触发器************/
SQL> create or replace trigger tr_system_error
  2  after servererror on database --关键字:servererror,操作一旦出错即触发
  3  declare
  4  begin
  5  insert into t_trace values(sys.login_user,sys_context('userenv','terminal'),sys_context('userenv','ip_address'),sysdate,sys.sysevent,dbms_utility.format_error_stack);
  6  end; --触发器不用显式commit
  7  /
 
Trigger created

/***********未错误操作前的审计表t_trace************/
SQL> select * from t_trace;
 
USERNAME   TERMINAL   IP                   TIME        ACTION                                                                           ERRORMSG
---------- ---------- -------------------- ----------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
 
/***********另一会话模拟一个错误操作/
SQL> drop table t_xs purge;
 
drop table t_xs purge
 
ORA-00942: table or view does not exist

/***********重新回到原会话错误操作已经捕获并记录在审计表**************/
SQL> select * from t_trace;
 
USERNAME   TERMINAL   IP                   TIME        ACTION                                                                           ERRORMSG
---------- ---------- -------------------- ----------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
TBL_BCK    123-PC     127.0.0.1            2013/4/18 1 SERVERERROR                                                                      ORA-00942: table or view does not exist
 

小结:1,servererror事件与触发器trigger

         2,sys_context函数

            sys_user函数

            sys_前缀的函数

        3,servererror可记录所有oracle操作同错的动作