How to compare multiple database schemas in Oracle using Toad®

In this tutorial we will learn how to compare multiple database schemas using Quest^® Toad^® for Oracle. Read the blog, then watch the video.

In previous versions of Toad for Oracle, the option Compare Multiple Schemaswas in a separate window, as one more option in the Database Menu. As of version 12.11, the Compare Multiple Schemas window was combined with the Compare Schemas window and now this window gives us the ability to perform various types of comparisons of multiple database schemas.

The different types of comparisons we can make are: 

1. One to One– compare a source schema or snapshot file to a target schema or snapshot file.
2. One to Many– compare a source schema or snapshot file to multiple target database schemas.
3. Many to Many Schemas– compare one or multiple pairs of database schemas in a source and target database.
4. Many to Many Snapshots– compare multiple pairs of snapshot files.

Database schemas case 1: Compare One to One

You can access my previous article where I explain step by step how to compare two database schemas (One to One) of Oracle databases using Toad for Oracle. This blog and accompanying video will show you how to One to Many; Many to Many Schemas and Many to Many Snapshots.

Database schemas case 2: Compare One to Many

The Compare Schemas tool is found in the main menu Database > Compare > Schemas.

Figure 1. TOAD Compare Schemas

As we have seen in the previous article, when accessing the tool, we can see that it is a simple three-step process that allows us to compare schemes.

In the first tab, click Choose Schemas

Figure 2. TOAD Compare Schemas, Choose Schemas

There are two ways to configure one-to-many comparisons. One is to simply click the green "+" button to add as many target schemas as we want, one at a time. We can select the destination of the schema to be from the database or a Snapshot file.

Figure 3: Add Schema – One to One

And the other way is, if we want to add multiple target database schemas at one time, we need to click Add multiple schemas in the dropdown menu.

Figure 4: Target: Add multiple schemas

Here we have two options.

Option 1: Select Same database in the case that we want to choose several database schemas from a single database. Then, we choose the target schemas for comparison and click OK.

Figure 5: Choose Schemas – Same database

Then we click on next.

Figure 6: Add Schemas – Same database

We select the options of the types of objects that we want to compare. In this example, we are going to select the following objects: grants, indexes, packages, procedures, sequences, tables, triggers, and views.

Figure 7: Choose Options – Select Object Types to Compare

All the options in the Choose Options section are explained in the first part of this article here.

We click on the Run button.

We can see the result for each of the selected database schemas at the end of the comparison.

Figure 8: Compare Schemas – Results (Target Schema 1)

Figure 9: Compare Schemas – Results (Target Schema 2)

Option 2: Select Different databases in the case of choosing the same schema in different databases. Then choose the connection and click the OK button.

Figure 10: Compare Schemas – Different Databases

Select the same types of objects in the previous example and click the Run button.

We can now see the results of the comparison of the same schema in different databases.

Select the Difference Summary tab for the first comparison and observe the differences between the source schema and the target schema.

Figure 11: Compare Schemas – Difference Summary Tab for XEPDB1 database target schema

We can review the comparison of the original schema with the second schema from another database in the same way.

Figure 13: Compare Schemas – Save Difference Summary

We navigate to the Sync Script tab where we can see the Script that Toad for Oracle has generated for us.

We also have the option to save the Script by clicking on the Save icon or send the Script to the Toad Editor by clicking on the Edit icon, and that way we can work with the Script.

Figure 14: Compare Schemas – Sync Script Tab

Database schemas case 3: Compare Many to Many

To compare pairs of database schemas in a source and target database, we choose <Multiple Schemas> from the Source Schema drop-down menu in the main window.

Figure 15: Compare Schemas – <Multiple Schemas>

This will cause the bottom half of the window to change for this type of outline selection style.

The option Match schemas automatically means that we do not need to manually choose the database schemas to compare. Toad for Oracle will automatically match them by name when the comparison runs.

Importantly, built-in schemas like SYS, SYSTEM, ORDSYS, etc. will be excluded. In case we want to see which are the schemas that Toad will ignore, we can search for MultipleSchemasToIgnore in the Toad.ini file where we can also edit the schema list.

If we want to have an idea of which schemas will be compared, we must uncheck the box Match schemas automatically and they will be shown below.

Figure 16: Compare Schemas – Uncheck the Match Schemas Automatically.

Then, we can re-check the box to activate the automatic mode if the connection of the destination schema is different from the connection of the source schema.

Figure 17: Compare Schemas – Check the Match Schemas Automatically.

To manually choose the database schemas that we want to compare, we must uncheck Match Schemas Automatically. Toad will display the schemas found on the source and target connections, and it will also check to see what it finds that exists in both schemas. We can change the Target schema if we want to match a source schema with a different name, and later we can check/uncheck in the Include column to indicate which pairs of database schemas we want to compare.

Figure 18: Compare Schemas – Manually select the schemes to compare.

Whichever option we choose to perform the comparison, either automatic or manual, we can use the Schema filter box to match the schemas to a particular pattern. For example, in Figure 19, we use the HR% filter to compare only the schemas that start with the letters HR.

Figure 19: Compare Schemas – Schema filter

Then, we continue with the wizard to view the results of the comparison.

Figure 20: Compare Schemas – Difference Summary

Database schemas case 4: Compare Many to Many Snapshots

To compare multiple sets of snapshot files, we choose Snapshot as the source schema and then Multiple Files from the Snapshot File drop-down menu.

Figure 22: Compare Schemas – Snapshot – Multiple Files

We click the "+" button to add a couple of snapshot files to the comparison. This dialog box will appear, where we can select the source file and the target file to make the comparison and click on the Ok button.

Figure 23: Add Files for Comparation

Then we can click the "+" button again to add another couple of snapshot files.

Figure 24: Compare Schemas – Specify Files View

Alternatively, if all our source files are in one folder and all our target files are in another folder, and the pairs of files to be compared have the same file names, we can check Specify Folders and then specify the source and target folders in the edit boxes shown below.

Figure 25: Compare Schemas – Specify Folders View

And then we would continue with the wizard to check the results of the different database schemas.

Conclusion

We have gone through the different ways that Toad for Oracle provides us to compare database schemas either from the same database or from different databases, as well as being able to compare different snapshots.

Knowing this great functionality of Toad for Oracle, helps both DBAs and developers to speed up the work when we need to determine the differences between one schema and another, which is important when determining the differences in our contrasting work environments.

I recommend that you try this Toad functionality and put it into practice so that it helps you in your day-to-day work to becoming more efficient and meeting your professional goals.

Watch the 5 minute video demo of this blog

Related information

Blog: How to compare two schemas in Oracle using Toad®

Video: How to compare database environments with multi-schema compare in Toad for Oracle

Have questions, comments? 

Head over to theToad for Oracle forumon Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!