最近ogg的灾备端复制进程中的一张表老是报错ORA-04031，但是又查不到原因，于是想用审计的方法来看到底这张表是被谁做了DML操作，把数据搞没了。本来想用数据库自带的审计功能参考：http://hbxztc.blog.51cto.com/1587495/1870181

但是需要重启数据库，就放弃了，上网查资料看到有人用触发器来实现这个功能，于是自己也做了尝试。

平台11.2.0.4

sys@ORCL>select*fromv$version;BANNER-------------------------------------------------------------------------------------OracleDatabase11gEnterpriseEditionRelease11.2.0.4.0-64bitProductionPL/SQLRelease11.2.0.4.0-ProductionCORE11.2.0.4.0ProductionTNSforLinux:Version11.2.0.4.0-ProductionNLSRTLVersion11.2.0.4.0-Production

创建测试表Orders

zx@ORCL>createtableORDERS2(3order_idNUMBER,4order_nameVARCHAR2(10)5);Tablecreated.

创建用于记录DML操作记录的表

zx@ORCL>createtableAUDIT_ORDERS2(3orderid_newNUMBER(38),4orderid_oldNUMBER(38),5usernameVARCHAR2(30),6opt_dateDATE,7opt_typeVARCHAR2(10),8terminalVARCHAR2(50),9session_idNUMBER(10),10hostnameVARCHAR2(50)11);Tablecreated.

创建触发器

zx@ORCL>CREATEORREPLACETRIGGERTRI_AUDIT_ORDERS2BEFOREINSERTORUPDATEORDELETEONORDERS3FOREACHROW4BEGIN5IFINSERTINGTHEN6INSERTINTOAUDIT_ORDERS7VALUES8(:NEW.ORDER_ID,9:OLD.ORDER_ID,10USER,11SYSDATE,--记录操作的时间12'INSERT',13SYS_CONTEXT('USERENV','TERMINAL'),--记录操作来源的终端信息14USERENV('SID'),--记录操作的SID15SYS_CONTEXT('USERENV','HOST'));--记录操作的主机名16ELSIFUPDATINGTHEN17INSERTINTOAUDIT_ORDERS18VALUES19(:NEW.ORDER_ID,20:OLD.ORDER_ID,21USER,22SYSDATE,23'UPDATE',24SYS_CONTEXT('USERENV','TERMINAL'),25USERENV('SID'),26SYS_CONTEXT('USERENV','HOST'));27ELSIFDELETINGTHEN28INSERTINTOAUDIT_ORDERS29VALUES30(:NEW.ORDER_ID,31:OLD.ORDER_ID,32USER,33SYSDATE,34'DELETE',35SYS_CONTEXT('USERENV','TERMINAL'),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/assysdbaConnected.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----------------------------------------3yhz10zx4wj--审计表记录SQL>colusernamefora10SQL>colhostnamefora20SQL>altersessionsetnls_date_format='yyyymmddhh34:mi:ss';会话已更改。SQL>setlinesize200SQL>select*fromaudit_orders;ORDERID_NEWORDERID_OLDUSERNAMEOPT_DATEOPT_TYPETERMINALSESSION_IDHOSTNAME-------------------------------------------------------------------------------------------------------------------------------------------3ZX2017010421:32:46INSERTVICTOR-PC10WORKGROUP\VICTOR-PC1ZX2017010421:30:32INSERTpts/024rhel62ZX2017010421:31:47INSERTVICTOR-PC146WORKGROUP\VICTOR-PC4SYS2017010421:33:52INSERTpts/024rhel6101ZX2017010421:37:26UPDATEpts/024rhel62ZX2017010421:37:50DELETEpts/024rhel6已选择6行。

审计表记录了所有的DML操作，可以用于审计哪些用户对表做了哪些操作。

参考：

http://www.cnblogs.com/wanglibo/articles/2121098.html

http://www.cnblogs.com/huyong/archive/2011/04/27/2030466.html