最近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
