Shulou(Shulou.com)06/01 Report--

Recently, a table in the replication process of the disaster recovery side of ogg always reported an error ORA-04031, but could not find out the reason, so I wanted to use the audit method to see who did the DML operation on this table and lost the data. Originally intended to use the audit function included in the database reference: http://hbxztc.blog.51cto.com/1587495/1870181

But need to restart the database, give up, look up information on the Internet to see someone using triggers to achieve this function, so I also made an attempt.

Platform 11.2.0.4

Sys@ORCL > select * from v$version BANNER-Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionPL/SQL Release 11.2.0.4.0-ProductionCORE 11 .2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0-ProductionNLSRTL Version 11.2.0.4.0-Production

Create a test table Orders

Zx@ORCL > create table ORDERS 2 (3 order_id NUMBER, 4 order_name VARCHAR2 (10) 5); Table created.

Create a table for recording DML operations

Zx@ORCL > create table AUDIT_ORDERS 2 (3 orderid_new NUMBER (38), 4 orderid_old NUMBER (38), 5 username VARCHAR2 (30), 6 opt_date DATE, 7 opt_type VARCHAR2 (10), 8 terminal VARCHAR2 (50), 9 session_id NUMBER (10), 10 hostname VARCHAR2 (50) 11); Table created.

Create trigger

Zx@ORCL > CREATE OR REPLACE TRIGGER TRI_AUDIT_ORDERS 2 BEFORE INSERT OR UPDATE OR DELETE ON ORDERS 3 FOR EACH ROW 4 BEGIN 5 IF INSERTING THEN 6 INSERT INTO AUDIT_ORDERS 7 VALUES 8 (: NEW.ORDER_ID, 9: OLD.ORDER_ID, 10 USER, 11 SYSDATE,-- record operation time 12 'INSERT', 13 SYS_CONTEXT (' USERENV' 'TERMINAL'),-- record the terminal information of the operation source 14 USERENV (' SID'),-- record the SID 15 SYS_CONTEXT of the operation ('USERENV',' HOST')) -- hostname 16 ELSIF UPDATING THEN 17 INSERT INTO AUDIT_ORDERS 18 VALUES 19 (: NEW.ORDER_ID, 20: OLD.ORDER_ID, 21 USER, 22 SYSDATE, 23 'UPDATE', 24 SYS_CONTEXT (' USERENV', 'TERMINAL'), 25 USERENV (' SID'), 26 SYS_CONTEXT ('USERENV',' HOST')) 27 ELSIF DELETING THEN 28 INSERT INTO AUDIT_ORDERS 29 VALUES 30 (: NEW.ORDER_ID, 31: OLD.ORDER_ID, 32 USER, 33 SYSDATE, 34 'DELETE', 35 SYS_CONTEXT (' USERENV', 'TERMINAL'), 36 USERENV (' SID'), 37 SYS_CONTEXT ('USERENV',' HOST')); 38 END IF; 39 END; 40 / Trigger created.

test data

-- sqlplus of linux inserts zx@ORCL > insert into orders values (1 row created.zx@ORCL > commit;Commit complete.--windows), sqlplus inserts SQL > insert into orders values (2); 1 line has been created. SQL > commit; submission completed. -- plsql inserts INSERT INTO orders VALUES (3); COMMIT;-- inserts zx@ORCL > conn / as sysdbaConnected.sys@ORCL > insert into zx.orders values (4) using sys users; 1 row created.sys@ORCL > commit;Commit complete.-- updates data zx@ORCL > update orders set order_id=10 where order_id=1;1 row updated.zx@ORCL > commit;Commit complete.-- deletes data zx@ORCL > delete from orders where order_idcommit;Commit complete.

View the records in the record table

-- Test table record zx@ORCL > select * from orders; ORDER_ID ORDER_NAME- 3 yhz 10 zx 4 wj-- audit table record SQL > col username for a10SQL > col hostname for a20SQL > alter session set nls_date_format='yyyymmdd hh34:mi:ss'; session has been changed. SQL > set linesize 200SQL > select * from audit_orders ORDERID_NEW ORDERID_OLD USERNAME OPT_DATE OPT_TYPE TERMINAL SESSION_ID HOSTNAME 3 ZX 20170104 21:32:46 INSERT VICTOR-PC 10 WORKGROUP\ VICTOR-PC 1 ZX 20170104 21:30:32 INSERT pts/0 24 rhel6 2 ZX 20170104 21:31:47 INSERT VICTOR-PC 146WORKGROUP\ VICTOR-PC 4 SYS 20170104 21:33:52 INSERT pts/0 24 rhel6 10 1 ZX 20170104 21:37:26 UPDATE pts/0 24 rhel6 2 ZX 20170104 21:37:50 DELETE pts / 024 rhel6 has selected 6 rows.

The audit table records all DML actions and can be used to audit which users have done what to the table.

Reference:

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

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.