At times the out of sync are known to occur after a certain time. Most common cause is removal of queue data due to some system, database or replication issues. But there can be other issues resulting in out of sync after a particular time such as user error, etc. The “where” clause in Repair comes handy when fixing out of sync on very large tables that would otherwise require a long time even with means other than Repair. The only pre-requisite is that the table should have a DATE type of field that logs the creation time of a row as well the time as and when that row is subsequently updated.
General information.
When running Repair on a very large table, it can fail due to any of a number of reasons. These can range from ORA-01652 (failed to extend TEMP segment) to Repair timing out. It can also hold up replication thru that Post queue as Post remains suspended till the Repair finishes thereby requiring it to be run only during quiet periods.
However, it is possible to limit the number of rows to be compared by using a “where” clause if the out of sync is known to occur after a certain date and if the table has a DATE type of field that gets populated every time a row is inserted or subsequently updated. Normally such large tables almost always have such a field and if they do not, then the article will not apply to them.
Assume that table named source_table has a DATE type of column named UPDATE_DATE which gets populated every time a row is inserted or subsequently updated. For simplicity sake assume that the source table is only replicating to one target. If the out of sync occurred after, say, October 18, 2016, then you can formulate the following Repair command to repair only those rows that were created or updated after October 18, 2016:
repair owner.source_table where "UPDATE_DATE > to_date('18-OCT-16', 'DD-MON-RR')"
It does not matter how large the table is, as Repair will only work on a very small subset of the rows due to a very narrow time span. Repair will quickly gather such small # of rows to do the comparison and repairing. Usually large tables keep track of the time when rows in it are first inserted or subsequently updated so this method works wonders.
The above method also applies for Compare.
