We have a unique situation and I was wondering if some of you could chime in. I am no way close to DBA and that's why I am here.
Let me illustrate what we are trying to achieve.
Let's say we have Table - A and we would like to replicate specific row transaction to Table B.
Here are the rows in Table A
Time: Lets say 15:00
A1 Just Updated @15:00
A2 Just inserted @15:01
B1 - Daily Delete Row -i.e just deleted a while back - Non scheduled process --executed by application @15:02
B3 - Daily Delete Row - i.e just deleted a while back -- Non Schduled process --executed by application @15:05
B4 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
B5 - Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
B6 -Just recently purged (As part of 180 Day purge ) - Scheduled process executed by operations team @15:10
Current Data in Table B (Before Replication)
A1 (without updates)
Expected rows in Table B (via replication/snapshot/materialized view / or any other method)
Replication at 15:30
Table B - Read Only
Expected rows after replication-
A1 -- Newly updated details
A2 -- Newly inserted row
B1 - Daily delete row is expected to be replicated
B3 - Daily delete row is expected to be replicated
***Note row B4 is not expected to be replicated to table B.
1) How can we get updates, inserts and daily deletes replicated while ignore large purges?
2) How can large purge changes be reflected in replicated tables as well without deleting daily deletes?
Well, if anyone of you experts can give further direction , it will very highly appreciated and remembered :)
Assuming that you can modify the purge process, you could use Streams and have the purge process set a tag that you could configure the capture process to exclude those changes from capture. Streams is being replaced going forward by GoldenGate (though GoldenGate requires additional licensing)-- GoldenGate likely has a similar mechanism to allow you to include or exclude certain transactions.