The new Oracle Data Compare from xSQL

A few days ago one of my friends Naim Sula, the CEO of xSQL Software contacted me. He said he had an interesting Oracle tool called as “Oracle Data Compare” - version 3 and he requested me to do a feature review of the tool. I was interested since I knew that this kind of feature of comparing schemas and data from different Oracle databases, is very important to DBAs who are involved in moving schema changes and reference data from development to testing, and then to production. 

As a DBA, in the early 2000s I used to compare schemas and data in development, test and production databases manually by writing SQL auto-generation scripts. I wanted a product that would help me with this kind of comparison so that I could complete my work faster, that’s how important such tools are.

Naim’s company xSQL was founded in 2003 and he says over 30,000 developers and DBAs around the world use his products, including Microsoft and NASA. The company itself is a Microsoft certified partner. This has subtly influenced the product, as we will see. Their older products are for MS SQL Server and for a similar purpose – schema compare and data compare. They are not an Oracle partner at the present time but hopefully will become one in the future.

Let’s take a quick view of their product. You can download Oracle Data Compare from here and use it for a trial period of 3 weeks, during which it is fully functional with no restrictions. After the 3-week trial, you need to purchase the license. What you get is a Windows installer, so the software gets installed on your Windows desktop. The requirement is that your PC must have the Microsoft .NET Framework 4 (full) installed.

The product works with Oracle databases version 9i, 10g, 11g and 12c and is primarily for comparing and synchronizing the data between Oracle databases. Version 3.0.0.0 of Oracle Data Compare was released very recently on July 03, 2017. This version supports the Managed Driver version (which is 100% .NET code) of the Oracle Data Access Provider for .NET (ODP.NET). This allows optimized ADO.NET data access to the Oracle database, and is available on the Oracle Technical network here.

You can use the GUI version of Oracle Data Compare (displayed below) and there is also a command line version, included in the same installer.

This screenshot shows the Data Compare workspace, where you add Oracle connections. You can save and load multiple workspaces. In each workspace, there is a comparison history which can retain the 25 most recent comparison sessions.

Once your Oracle connections have been added, you can start comparing the data in two schemas. The databases can be of different versions of flavors or Oracle.  Click on the “Compare Schemas” or “Compare Tables/Views” button at the top of the screen.

The following screenshot shows the selection of tables or views for the purpose of the comparison.

You can select different data compare options, such as synchronize rows that are different, or synchronize tows that exist on the left database or on the right database only. You can also generate a synchronization script and limit its size, up to 2048 MB.

When full schemas are compared, it will first perform an automatic mapping of the tables based on their names. You can customize this object mapping if you want.

You can also include a where clause for large tables, to limit the number of rows that will be compared. The comparison completes and displays the results as follows.

Once the data comparison has completed, you can start the synchronization. The tables with differences and the different rows are earmarked automatically for synchronization, but you can customize which tables and which rows should be included. As the next step, you can generate the synchronization scripts.

The scripts can also be viewed and verified, and then executed. You can then save the comparison sessions in a workspace.

As mentioned previously, there is also a Command Line version of Data Compare, via which you can perform automated data comparison and synchronization, and schedule this as well. Sample configuration files are provided for the most typical data comparison scenarios. These are located on \Oracle Data Compare\CommandLine Samples\ folder under the main installation folder of Oracle Data Compare: \Program Files\xSQL Software\Oracle Data Compare\.

More information and on-line help for the command line and also the GUI product is provided here.

I found what is missing at this stage is a Schema structure compare – this would compare the SQL structure of tables and views in different schemas of different database such as development, test or production; and then generate scripts in order to perform DDL which would modify the structure in the target schema. For example, a developer could have added new columns that should be carried over to test and then to production.

Such a tool could also compare Oracle user privileges/roles in different databases, as well as compare stored PL/SQL procedures/functions/packages. This kind of Oracle privilege/role comparison is important to find out if fewer privileges are causing an application to fail in production. This could very well happen if the developers had given excessive rights to their schemas in development and test.

Naim tells me all that may be coming soon. Well, DBAs will certainly like the Oracle Data compare tool, and can look forward to the coming Oracle Schema / Structure compare tool.