Comparing Diffs Between Two Oracle Database Schemas

When developing databases, we frequently need to check what changes were made to its schema, if any mistakes were made, and, if there are any, we should roll back to the working version saved in the source control system. To do this, it’s enough to compare schemas of version 1.1 and 1.2, and generate an UPDATE SCRIPT using any Database Diff tool, including our dbForge Schema Compare for Oracle.

So what functionality should be available in such tool?

Firstly, the tool should be easy-to-use and user-friendly. A user should not be obliged to learn a pile of topics in the tool documentation before using it. It should be enough to perform the following set of steps:
1) Select Source and Target
2) Select a schema or several schemas (if the tool provides such possibility) for comparison
3) Tune comparison process by checking the needed options (an optional step)
4) View comparison results
5) Tune script generation options (an optional step)
6) Generate an update script and/or execute it directly to the target database
Secondly, the tool should support comparison of all object types you might have in your schemas – otherwise, how the tool is supposed to look for differences in them? And, at last, the tool should be fast. This point is especially important for searching data differences when a comparison of several schemas is performed, or the size of the project itself is large.

Now let’s look at Oracle SQL Developer and its alternative — dbForge Schema Compare for Oracle. Both are offering schema comparison functionality.
Oracle SQL Developer

Schema Comparison Wizard of Oracle SQL Developer can be opened through the Tools -> Database Diff menu. After you click this menu item, you’ll get a message telling you that it’s using Oracle Change Management, a payable option of the Oracle database, and you have to acknowledge you have a proper license to use that.

After that, you should choose Source Connection and Target Connection from the existing connections. Also, you can choose object types to compare during this step. Available object types are Tables, Views, Indexes, Package Spec, Package Body, Procedure, Functions, Triggers, Types, Sequence, Materialized View, Materialized View Logs, Synonyms, Database Link.

Press the Next button to open the Specify Objects page, where you can select objects for comparison and filter them by their type. Then press the Next button to open the Diff Summary page, where the Finish button becomes active. Then Press the Finish button.
After comparison process is completed (it did not take much time), the result is shown as Diff Report.

Perfect, Oracle SQL Developer managed to compare test schemas, but it did not meet the requirements we specified at the beginning of the article. These are:
1. The tool needs availability of the Change Management license for your Oracle database. The price depends on your Oracle Database license, but it is quite high if you need to compare only a couple of schemas using the free SQL Developer.
2. There is no possibility to compare several schemas at once.
3. There are no comparison options as such so you cannot tune comparison process.
4. There is only the number of differences displayed in the provided Diff Report, but they cannot be viewed in the side-by-side DDL Diff. Also, Diff Report provides an Update script only for the single object selected in the grid and does not allow viewing the generated update script for several objects.
5. There is no Synchronization wizard with options for tuning synchronization process.

As we see, there is quite a lot of requirements the tool doesn’t answer, isn’t there? We did our best to correct all these shortcomings in our tool for schema comparison.

dbForge Schema Compare for Oracle

Let’s run the dbForge Schema Compare for Oracle — Oracle SQL Developer alternative, and press New Schema Comparison on Start Page. After this, we should create connections for source and target (this is impossible in Oracle SQL Developer). Schemas with identical names will be mapped automatically. Schemas also can be mapped manually, and several schemas can be included in comparison.

On the next two pages you can accurately tune the comparison process with the help of options and select the needed types for comparison of their DDL: Tables, Views, Packages, Procedures, Functions, Triggers, Array Types, Object Types, Table Types, Sequences, Materialized Views, Materialized View Logs, Synonyms, Database Links, XML Schemas. Unlike in Oracle SQL Developer, the Compare button is available on the first page of the wizard.

After the comparison process is completed, a user-friendly document with comparison results is shown. In this document, you can inspect the differences visually, view the update script that can be generated for several objects selected in the grid at once, include and exclude the needed objects from synchronization, filter or group objects. It’s enough to call the corresponding Synchronization wizard to perform synchronization.

Synchronization Wizard allows selecting the path to save the generated script to disk, executing this script directly to the target database, tuning the synchronization process accurately, generating the update script, viewing warnings and action plan.

Well, let’s sum up everything.
Oracle SQL Developer analog, developed by Devart answered the requirements we’ve specified in the beginning. However, dbForge Schema Compare for Oracle cannot be used in the continuous process of database development for Oracle, because it does not provide the possibility of performing comparison process and generating update script through the command line. Yes, it is so in the first version, but we’ve taken this fact into account and it will be corrected in the next version of the product.