Tuesday, February 9, 2021

Data Compare for Oracle - Custom Table Mapping

The default behavior of xSQL Software's Data Compare for Oracle has been carefully designed to minimize the need for user intervention in most common cases. Generally, the most common data compare scenario involves two databases with identical or at least similar schemas (same tables and views), therefore, when you click on "compare databases" (in the case of Oracle the button is actually "Compare Schemas"), after you select the two databases/schemas to be compared, the tool will automatically map (pair) the tables and views based on the name. 

However, there are cases when you may need to compare data between two databases that do not share the same schema. That is, you want to compare the data in two tables with different names, and those tables may be in the same schema/database or in different schemas/databases. In this case, instead of clicking on "Compare Schemas" you would click on "Compare Tables/Views" - that invokes the manual mapping / pairing functionality that allows you to pair any two tables regardless of their name. 

The mapping process is very simple: 

  • click on "Compare Tables/Views" button on the "Home" tab of the ribbon. 
  • On the "Object Compare" dialog window that appears first select the Oracle connections and schemas on which the objects reside (you can choose the same database / schema on both sides if necessary)
  • When there is a large number of objects you may wish to specify a pattern for the name of the objects you are looking for (ex. all tables that start with a) - that will make it easier for you to find the tables/views you wish to compare. 
  • Once you have made your selections, click on the "Read Database Objects" link. Data Compare for Oracle will read objects on each schema and populate the two left bottom windows with the list of tables / views available.
  • Now you can map those tables/views one at a time irrespective of the names. Select an object on the left, then select an object on the right and click the left to right "arrow" button to map those two objects.

NOTE: In order to compare the Oracle views you will need to check the data comparison option "Compare and Synchronize Views". Keep in mind that Oracle imposes limitations on views and not all views are updatable. Also be aware of the fact that updating views means updating the underlying tables so use this option with caution.

Download Data Compare for Oracle and try today. 


Post a Comment