Impact on CDC Replicate Tasks when Dropping Partition from Oracle Table

[NewbieQlik]

we have a qlik replicate CDC task configured to ingest the data from oracle source to snowflake target in near real time manner.  All the tables that we have in the tasks are partitioned based on YYYYMM. Last year we did the initial full load on all these tables and from that point onwards we are receiving the CDC changes.

Over a period of time the size of the table has grown bigger and now we have decided to clean up some of the old transactions from the table. As part of the clean up exercise we have decided to drop older partitions by altering the tables.

If we alter the table and drop old partition will there be any impact on the CDC task which are currently active with those tables.

This is what we are planning to do.

1. Stop the application and make sure there are no activities on the oracle DB

2. Stop the CDC task

3. Drop partitions (by executing alter table drop partition command)

4. Resume the CDC task

Please let me know if there is any impact.

[Alan_Wang]

Hi @NewbieQlik 

Please note the following limitations on Oracle partitions.

• Data changes resulting from partition/sub-partition operations (ADD, DROP, EXCHANGE and TRUNCATE) will not be replicated and may cause the following errors:
  • For ADD operations, updates and deletes on the added data may return a "0 rows affected" warning.
  • For DROP and TRUNCATE operations, new inserts may result in "duplicates" errors.

  • For EXCHANGE operations, both a "0 rows affected" warning and "duplicates" errors may be encountered.

  To replicate changes resulting from partition/sub-partition operations, you need to reload the tables in question. When adding a new empty partition, operations on the newly added partition will be replicated to the target as normal.

  https://help.qlik.com/en-US/replicate/May2021/Content/Replicate/Main/Oracle/OracleDB_source_limitati...