The Data Compare for Oracle is designed to compare the data in "matching" rows in two tables that may be in two different databases or even the same database. What does "matching" rows mean? Matching rows means rows from both tables, that can be uniquely paired to each other based on the value of a certain column or a combination of columns.
The most common scenario is that in which you would pair the rows from both sides based on the value of the Primary Key. This works great when you are comparing tables from identical or similar schemas / databases, but that is not always the case. Regardless, after pairing the tables (either automatically or manually), the Data Compare for Oracle analyses the tables and tries to identify an identical unique key that exist on both tables. The unique key can be the Primary Key, a Unique Constraint or a Unique Index. If it finds one, that is the key that will be used to pair the rows to each other, and if it does not find one it marks the table pair as un-comparable. Whether a useful comparison key was automatically identified or not you can still drill down on that table pair and either pick a different comparison key from the list of identified unique keys, or create a custom comparison key.
- User-Defined Key
- Primary Key
- Unique Constraint (the 1st in alpha order)
- Unique Index (the 1st in alpha order)
- Columns in the custom keys must have the same name.
- Columns in the custom keys must have the same data type. Length, scale, precision, nullability and other column properties are not considered.
- The uniqueness of the key is checked only if the option “Check custom key uniqueness”, in the Application Settings form, is on.