SERTDELETES | NOINSERTDELETES

Valid for Replicat

Use the INSERTDELETES and NOINSERTDELETES parameters to control whether or not Oracle

GoldenGate converts source delete operations to insert operations on the target database.

The parametersare table-specific. One parameter remains in effect for all subsequent MAP

statements,until the other parameter is encountered.

When using INSERTDELETES, usethe NOCOMPRESSDELETES parameter so that Extract does not

compressdeletes.

Default NOINSERTDELETES

Syntax INSERTDELETES | NOINSERTDELETES

也就是说：此参数可以使得目标表中插入源表中被删除的记录，多用于业务跟踪或审计。请参考以下测试：

[sql] view plain copy

-==source==-
SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
SQL>
extract ext1
extract pum1
-==target==-
SQL> desc user2.t1hist
Name Null? Type
----------------------------------------- -------- ----------------------------
TS DATE
BEFORE_AFTER VARCHAR2(200)
OP_TYPE VARCHAR2(20)
ID NUMBER
SQL>
SQL> truncate table user2.t1hist;
Table truncated.
SQL>
replicat rep1
:::
MAP user1.*, TARGET user1.*;
INSERTDELETES
MAP user1.t1, TARGET user2.t1hist,
COLMAP (TS = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
BEFORE_AFTER = @GETENV ("GGHEADER", "BEFOREAFTERINDICATOR"),
OP_TYPE = @GETENV ("GGHEADER", "OPTYPE"),
ID = ID);
-==source==-
SQL> delete t1 where id = 2000;
8 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from t1;
COUNT(*)
----------
110584
SQL>
Extracting from USER1.T1 to USER1.T1:
*** Total statistics since 2015-08-18 16:31:50 ***
Total inserts 110592.00
Total updates 0.00
Total deletes 8.00
Total discards 0.00
Total operations 110600.00
*** Daily statistics since 2015-08-18 16:31:50 ***
Total inserts 110592.00
Total updates 0.00
Total deletes 8.00
Total discards 0.00
Total operations 110600.00
*** Hourly statistics since 2015-08-18 17:00:00 ***
Total inserts 82944.00
Total updates 0.00
Total deletes 8.00
Total discards 0.00
Total operations 82952.00
*** Latest statistics since 2015-08-18 16:31:50 ***
Total inserts 110592.00
Total updates 0.00
Total deletes 8.00
Total discards 0.00
Total operations 110600.00
End of Statistics.
-==target==-
SQL> select count(*) from user2.t1hist;
COUNT(*)
----------
110600
SQL>
SQL> l
1* select * from user2.t1hist where id = 2000
SQL> /
TS BEFORE_AFTER OP_TYPE ID
------------ -------------------- ---------------------------------------- ----------
06-JAN-16 AFTER INSERT 2000
06-JAN-16 AFTER INSERT 2000
06-JAN-16 AFTER INSERT 2000
06-JAN-16 AFTER INSERT 2000
06-JAN-16 AFTER INSERT 2000
06-JAN-16 AFTER INSERT 2000
06-JAN-16 AFTER INSERT 2000
06-JAN-16 BEFORE DELETE 2000
06-JAN-16 BEFORE DELETE 2000
06-JAN-16 BEFORE DELETE 2000
06-JAN-16 BEFORE DELETE 2000
TS BEFORE_AFTER OP_TYPE ID
------------ -------------------- ---------------------------------------- ----------
06-JAN-16 BEFORE DELETE 2000
06-JAN-16 BEFORE DELETE 2000
06-JAN-16 BEFORE DELETE 2000
06-JAN-16 BEFORE DELETE 2000
06-JAN-16 AFTER INSERT 2000
16 rows selected.
SQL>
GGSCI (target) 23> stats *
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2016-01-06 08:02:22.
Replicating from USER1.T1 to USER1.T1:
*** Total statistics since 2016-01-06 07:12:10 ***
Total inserts 110592.00
Total updates 0.00
Total deletes 8.00
Total discards 0.00
Total operations 110600.00
*** Daily statistics since 2016-01-06 07:12:10 ***
Total inserts 110592.00
Total updates 0.00
Total deletes 8.00
Total discards 0.00
Total operations 110600.00
*** Hourly statistics since 2016-01-06 08:00:00 ***
Total inserts 0.00
Total updates 0.00
Total deletes 8.00
Total discards 0.00
Total operations 8.00
*** Latest statistics since 2016-01-06 07:12:10 ***
Total inserts 110592.00
Total updates 0.00
Total deletes 8.00
Total discards 0.00
Total operations 110600.00
Replicating from USER1.T1 to USER2.T1HIST:
*** Total statistics since 2016-01-06 07:12:10 ***
Total inserts 110592.00
Total updates 0.00
Total deletes 8.00
Total discards 0.00
Total operations 110600.00
*** Daily statistics since 2016-01-06 07:12:10 ***
Total inserts 110592.00
Total updates 0.00
Total deletes 8.00
Total discards 0.00
Total operations 110600.00
*** Hourly statistics since 2016-01-06 08:00:00 ***
Total inserts 0.00
Total updates 0.00
Total deletes 8.00
Total discards 0.00
Total operations 8.00
*** Latest statistics since 2016-01-06 07:12:10 ***
Total inserts 110592.00
Total updates 0.00
Total deletes 8.00
Total discards 0.00
Total operations 110600.00
End of Statistics.

结论：源表中删除了8条，剩余110584条；目标表中多了16条，包括删除前和删除后的记录。

ITPub博客

INSERTDELETES&NOINSERTDELETES--GoldenGate Parameter

SERTDELETES | NOINSERTDELETES

最新文章