Options for syncing from a MASTER table to a COPY table?

Question

I have a MASTER table and a COPY table in separate databases (both are Oracle 12c).

I want to sync the MASTER table with the COPY table on a weekly basis.

---

I can think of a few options for syncing from MASTER to COPY:

• Sync All: Delete all from COPY and insert all from MASTER.
• Period-based: Replace (or add) rows in COPY where sysdate - editdate < 7 in MASTER.
• Period-based with redundancy: Replace (or add) rows in COPY where sysdate - editdate < 31 in MASTER.
  • This applies in situations where rows in previous syncs have failed. The failed rows in MASTER will have been corrected, but we still need to sync them to COPY in future syncs.
• Manual flag: Users manually flag rows in MASTER that need to by synced to COPY. The sync process resets the flag in MASTER once the edits have been synced to COPY.
• Date comparison: If date in MASTER is newer than date in COPY then sync those rows.

---

Are there any other options or details that I've missed?

Answer 1

Make the copy a materialised view over a DB link, then you don't have to worry about doing the tracking yourself - Oracle will do it for you. - user5555

Answer 2

I'm going to put forward another option here, even though I tend to agree with User555's answer of Mat view via db link.

GoldenGate may be an available option for you in Oracle 12, if you happen to have license for it.

This would allow you to setup an extract/replicat, effectively keeping the table up-to-date within a few seconds of the other table.

This option might actually have a few benefits over the mat view option, however, I'm personally unsure which would prove to be more "stable" or reliable in long run. :) Likely depends on your system setup.