No keys on primary with duplicate rows
A third party application has tables with no primary or unique keys and there are duplicate rows of data in the table. How can Replicate handle this if we want all rows updated on the target when we update all rows on source?
There is a business decision that needs to be made. If an update is done on a source table (no unique/no primary key) that updates 3 rows, is it desired to have the same change made on the target?
- If so, then you would need to set up a conflict hander for these tables with no primary keys or unique keys.
- If not, then these would need to be manually resolved because Replicate will see this as a conflict and pause the APPLY process until action can be taken manually.
To propagate this multiple row update for a table, the conflict handler that would be needed is below. If you have a set you can put them in a file and call that file. For example:
dbvrep> SET_CONFLICT_HANDLERS FOR TABLE <owner.tablename> FOR DELETE ON TOO_MANY TO SQL s/$/ and rownum = 1/
dbvrep> SET_CONFLICT_HANDLERS FOR TABLE <owner.tablename> FOR UPDATE ON TOO_MANY TO SQL s/$/ and rownum = 1/
dbvrep> shutdown apply
Restart APPLY from command line on the target host.
Please also refer to the User Guide section below for more detail:
General information on Conflict Resolution can also be found in the User Guide:
Please sign in to leave a comment.