最近ogg的灾备端复制进程中的一张表老是报错ORA-04031，但是又查不到原因，于是想用审计的方法来看到底这张表是被谁做了DML操作，把数据搞没了。本来想用数据库自带的审计功能参考：http://www.jb51.cc/article/p-zchlscoj-xe.html
但是需要重启数据库，就放弃了，上网查资料看到有人用触发器来实现这个功能，于是自己也做了尝试。
平台11.2.0.4
sys@ORCL>select*fromv$version; BANNER ------------------------------------------------------------------------------------- OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProduction PL/sqlRelease11.2.0.4.0-Production CORE11.2.0.4.0Production TNSforLinux:Version11.2.0.4.0-Production NLSRTLVersion11.2.0.4.0-Production
创建测试表Orders
zx@ORCL>createtableORDERS 2( 3order_idNUMBER,4order_nameVARCHAR2(10) 5); Tablecreated.
创建用于记录DML操作记录的表
zx@ORCL>createtableAUDIT_ORDERS 2( 3orderid_newNUMBER(38),4orderid_oldNUMBER(38),5usernameVARCHAR2(30),6opt_dateDATE,7opt_typeVARCHAR2(10),8terminalVARCHAR2(20),9session_idNUMBER(10),10hostnameVARCHAR2(20) 11); Tablecreated.
创建触发器
zx@ORCL>CREATEORREPLACETRIGGERTRI_AUDIT_ORDERS
2BEFOREINSERTORUPDATEORDELETEONORDERS
3FOREACHROW
4BEGIN
5IFINSERTINGTHEN
6INSERTINTOAUDIT_ORDERS
7VALUES
8(:NEW.ORDER_ID,9:OLD.ORDER_ID,10USER,11SYSDATE,--记录操作的时间
12'INSERT',13SYS_CONTEXT('USERENV','TERMINAL'),--记录操作来源的终端信息
14USERENV('SID'),--记录操作的SID
15SYS_CONTEXT('USERENV','HOST'));--记录操作的主机名
16ELSIFUPDATINGTHEN
17INSERTINTOAUDIT_ORDERS
18VALUES
19(:NEW.ORDER_ID,20:OLD.ORDER_ID,21USER,22SYSDATE,23'UPDATE',24SYS_CONTEXT('USERENV',25USERENV('SID'),26SYS_CONTEXT('USERENV','HOST'));
27ELSIFDELETINGTHEN
28INSERTINTOAUDIT_ORDERS
29VALUES
30(:NEW.ORDER_ID,31:OLD.ORDER_ID,32USER,33SYSDATE,34'DELETE',35SYS_CONTEXT('USERENV',36USERENV('SID'),37SYS_CONTEXT('USERENV','HOST'));
38ENDIF;
39END;
40/
Triggercreated.
测试数据
--linux的sqlplus插入 zx@ORCL>insertintoordersvalues(1,'zx'); 1rowcreated. zx@ORCL>commit; Commitcomplete. --windows的sqlplus插入 sql>insertintoordersvalues(2,'wl'); 已创建1行。 sql>commit; 提交完成。 --plsql插入 INSERTINTOordersVALUES(3,'yhz'); COMMIT; --使用sys用户插入 zx@ORCL>conn/assysdba Connected. sys@ORCL>insertintozx.ordersvalues(4,'wj'); 1rowcreated. sys@ORCL>commit; Commitcomplete. --更新数据 zx@ORCL>updateordeRSSetorder_id=10whereorder_id=1; 1rowupdated. zx@ORCL>commit; Commitcomplete. --删除数据 zx@ORCL>deletefromorderswhereorder_id<3; 1rowdeleted. zx@ORCL>commit; Commitcomplete.
查看记录表中的记录
--测试表记录 zx@ORCL>select*fromorders; ORDER_IDORDER_NAME ---------------------------------------- 3yhz 10zx 4wj --审计表记录 sql>colusernamefora10 sql>colhostnamefora20 sql>altersessionsetnls_date_format='yyyymmddhh24:mi:ss'; 会话已更改。 sql>setlinesize200 sql>select*fromaudit_orders; ORDERID_NEWORDERID_OLDUSERNAMEOPT_DATE OPT_TYPE TERMINAL SESSION_IDHOSTNAME ------------------------------------------------------------------------------------------------------------------------------------------- 3 ZX 2017010421:32:46INSERT VICTOR-PC 10WORKGROUP\VICTOR-PC 1 ZX 2017010421:30:32INSERT pts/0 24rhel6 2 ZX 2017010421:31:47INSERT VICTOR-PC 146WORKGROUP\VICTOR-PC 4 SYS 2017010421:33:52INSERT pts/0 24rhel6 10 1ZX 2017010421:37:26UPDATE pts/0 24rhel6 2ZX 2017010421:37:50DELETE pts/0 24rhel6 已选择6行。
审计表记录了所有的DML操作，可以用于审计哪些用户对表做了哪些操作。
参考：
http://www.cnblogs.com/wanglibo/articles/2121098.html
http://www.cnblogs.com/huyong/archive/2011/04/27/2030466.html
