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條，包括刪除前和刪除後的記錄。

INSERTDELETES&NOINSERTDELETES--GoldenGate Parameter

SERTDELETES | NOINSERTDELETES

相關文章