Toad World Blog

How to compare multiple database schemas in Oracle using Toad®

Mar 3, 2021 1:00:00 AM by Clarisa Maman Orfali

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 Schemas was 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.

Register now for Quest EMPOWER, November 10-11, 2021.

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

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

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

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

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

Figure 5: Choose Schemas – Same database

 

Then we click on next.

Figure 6: Add Schemas – Same database

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

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 8: Compare Schemas – Results (Target Schema 1)

 

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

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

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

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

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

Figure 14: Compare Schemas – Sync Script Tab

 

Try Toad for Oracle now

Try Toad for Oracle free for 30 days. No credit card required.

Already in a trial? Talk to sales or buy now online.

Already a loyal fan of Toad for Oracle? Renew now.

 

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.

img15-1

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.

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.

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.

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

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

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

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

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

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.

img25

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

Maman_Orfali_Engineering_February_How_to_compare_multiple_schemas

 

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 the Toad for Oracle forum on 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!

 

Tags: Toad for Oracle Toad for Oracle Videos database schema

Clarisa Maman Orfali

Written by Clarisa Maman Orfali

Clarisa is a System Engineer with more than 24 years of experience as a developer, teacher, and consultant in Information Technology. She was a Founder and CEO between 2013 to 2020 at ClarTech Solutions, Inc., a consulting firm specializing in Oracle APEX and Open Source technologies. Clarisa entered the world of Oracle technologies in 2009. After a few years, she discovered the power of application development with Oracle Application Express (APEX) and specialized in that area.

Clarisa is from Argentina, and she is living and working in Irvine, California, in the United States. She's also a Co-Founder of the Argentina Oracle User Group (AROUG). In addition, she had actively participated in large and popular events such as the OTN Tour Argentina and Oracle APEX Tour Latin America after called Oracle Developer Tour. She's also authored the first three Spanish books about Oracle Application Express and recorded and published several Online Video Courses.

She has a great passion for Oracle technologies and wants to expand that passion and influence, transferring her knowledge and experience to the entire Latin American and worldwide Oracle communities. You can always contact her through her Twitter account (@Clari707).

Clarisa loves spending time with her family by enjoying outdoor activities with her two adored children, Melanie and Nicolas, as well as hanging out with her friends. Also, she loves traveling with her husband Julio worldwide and to takes new challenges at every moment of her life.