After compare/repairing the Table it still shows that the table is out of sync when compare is run again.
The problem is with one of the internal queries made by Compare. Compare queries the database for the column number (position) of the key columns.
The problem occurs because the query Compare uses returns the column number for columns that have foreign key constraints. The column numbers returned are used in the order by section when querying the rows for the table to compare.
You can verify this by running the query below on the source database:
SQL> select COLUMN_NAME, POSITION from all_cons_columns where TABLE_NAME=<'TABLENAME'> and OWNER=<'OWNER'> order by POSITION;
COLUMN_NAME POSITION
------------------------------ ----------
ACCOUNT_ID 1
ACCOUNT_ID 1
CONTACT_ID 2
ACCOUNT_ID
CONTACT_ID
The "ACCOUNT_ID" appeared twice at the beginning and compare uses it in "order by" .The compare select statement looks like this "select ..... order by 1,1" and it fails to "order by" the data correctly.This is the reason for the false OOS.
WORKAROUND:
Try using orderby "c1,c2" in compare command where c1 and c2 are the primary key columns
sp_ctrl> compare table owner.table_name to owner.table_name target target_host@o.targetSID orderby "c1,c2"
STATUS:
This issue is fixed in Shareplex 5.3.2 patch release.
