How to compare two schemas in Oracle using Toad®

Are you a developer? Learn to compare two Oracle database schemas using Quest® Toad® for Oracle.

Suppose we are developing a web application using the Oracle database as our data storage and we find ourselves:

• In the need to replicate our development environment for testing and implementation without impacting the main development environment
• Wanting to duplicate an environment to work with some specific requirement
• Wanting (as a developer) to build a lab for research and study.
• Needing (as a DBA to be able to guarantee that the production environment has not changed and/or want to know how the development environment differs from the production environment

If you find yourself in any of the above situations, this article is for you. I will guide you step by step on how we can compare two Oracle database schemas using Toad® by Quest® for Oracle.

Several years ago, when I started in Oracle technologies, my first contact was in Database Administration and in those days I remember that as I was getting started in that new world, I typically would just manage few objects from the Oracle database—such as tables, indexes, synonyms, views—and could generate simple scripts to be able to determine the differences in both environments without much difficulty. Of course, today, when working on large projects, complexity has greatly increased as there can be so many different and interrelated object types that it is no longer possible to simply use simple, self-made scripts. To solve this situation, we have a perfect solution available from Toad for Oracle, which is the Compare Schemas tool. Toad for Oracle offers many different editions, the higher the edition, the more features and capabilities. Luckily, the Compare Schemas tool is available in the Base Edition, which means its available in every edition. Later on in this example, I also show you the option to synchronize script. This feature is available only in the Xpert or DBA editions.

Step by step: how to compare two schemas

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

Figure 1. Toad Compare Schemas

Upon accessing the tool, we can see that it is a simple three-step process that allows us to successfully compare even the most complex schemas.

Step 2: In the first tab, click Choose Schemas

Step 3: Delete Target Schema

Step 4: Select the source schema HRDEV (green color) from the Database option and select the target schema HRPROD (blue color) by clicking on the green plus button as shown in Figure 4.

Step 5: Click on the Next button which will open the Choose Options screen:

Figure 5. Toad Choose Options Screen – Compare à Object Types

As we can see there is a tree type view with three main nodes in which each one has their own options to define. In the main Compare node, we have two options.

The first option is Object Types in which we can select what types of objects from the Oracle database we want to compare, as shown in Figure 5. If we reduce the number of types of objects that we want to compare, the time it takes to complete the comparison reduces.

This screen offers context menu options for Check all and Uncheck all. Besides being able to select Expand all or Collapse all, this is very useful when working with tree-type views.

Then in the second Output option, we can specify a folder name for the synchronization script that will be generated, in my case test1, in addition to being able to configure whether we want the results of the comparison to be available both in a file and to be able to send it by email, as well as shown in Figure 6. For this step, you will need to be using Toad for Oracle Xpert Edition or with the DB Admin Module, as the synchronization feature is only available in these editions.

Figure 6. Toad Choose Options – Compare à Output screen

The only consideration here is that to send the results by email we need to have the email options configured. To do this, we access the menu View > Toad Options… > Email.

In the pop-up window enter the email configuration data in the Outgoing Mail Server section and in the Email Notifications section select Compare Schemas from the Window selection list, as shown in Figure 7.

Figure 7. Toad Menu View à Toad Options… à Email screen

On the other hand, on the Output screen, if we have a version control, we can also specify it on this screen.

Next, in the second main Options node of Compare Schemas, it allows us to select the control options of the comparison process, we have three options to configure. The Objects, Storage and Script options.

The Objects option allows us to specify what type of Oracle database objects we want to be compared and what types of objects we do not want to have in the comparison. The options are self-explanatory, for example we can exclude blanks, double quotes, comments, and other things in PL/SQL code packages as shown in Figure 8.

Figure 8. Toad Choose Options – Options à Objects screen

Under the Storage option, we can choose to ignore the storage clauses, as shown in Figure 9, in cases for example when the development database has a different size than production and we do not need to make such a comparison.

Figure 9. Toad Choose Options – Options à Storage screen

And finally, for this section we have the Script option, as shown in Figure 10, in which we define the values according to what we want to compare.

Figure 10. Toad Choose Options – Options à Script screen

Next, we move on to the third main Filters node.

The Filter by object name: LIKE option allows us to filter using simple Oracle pattern matches (for example, LIKE 'A%;B%'). And the box Maximum number of differences allows us to define after how many differences the comparison process ends. This is very useful when we have a lot of differences and we are willing to stop after a certain threshold.

In the case that we had the schema comparison exclusion file (.sce) we can select it from the Exclude File option.

To compare all the objects selected in the Object Type tab, we must have the Object Set checkbox deselected, as we see in Figure 11. Otherwise we can select the set of objects to compare in the section below.

Step 6: Click the Run button and wait for the results as shown in Figure 12.
Figure 12. Toad Compare Schemas – Difference Details tab

Let us keep in mind that there are three results tabs to choose from. The Difference Details tab allows us to easily view and navigate through all individual difference results, as well as offering the ability to view the SQL required to sync only a selected difference as shown in Figure 13.

Figure 13. Toad Compare Schemas - Individual Sync Code

Looking again at Figure 12, we can see that the Difference Details tab presents three main categories of difference: objects at source, but not at target; objects at target, but not at source; and objects in both schemas that differ. This is where we can see the differences between the development database and the production database.

The Difference Summary tab shows the same information in a rich text format suitable for printing as seen in Figure 14.

Figure 14. Toad Compare Schemas – Difference Summary tab

Finally, we can use the Sync Script tab (available with the Xpert or DBA editions) to see all the generated DDL (Data Definition Language) code to synchronize the source and target. We can save the script to a file, print it or just execute it on Toad.

It's important to mention that these scripts can be huge, so the recommendation is to save them to a file and then use the Toad SQL Editor to run the statements "little by little." That way we can control and monitor the entire process.

It is very risky to execute a large script blindly, knowing that it can alter our database. Needless to say, it is not a good practice to do so and it is better to always be very careful when we are working with our database. An example synchronization script is shown in Figure 15.

Receiving email with the results of the schema comparison

Remember that we have configured our email to receive the comparison script.

This is how the email would be displayed in our inbox as shown in Figure 16.

Figure 16. Toad Compare Schemas Results – Email

In the compressed file we receive 3 files:

• Schema Source vs Schema Target Details (HTML)
• Schema Source vs Schema Target Summary (HTML)
• Schema Source vs Schema Target Synchronization Script

Conclusion

As we can see throughout this article, the comparison of schemas in an Oracle database is a very simple task when we use Toad for Oracle, which greatly facilitates the task by shortening time and minimizing errors, both to replicate new environments or synchronize different environments while we are developing our applications. In this article I used advanced developer features. If all you need to do is compare schema, you should check out Toad for Oracle Base Edition. Quest just launched this toolset as a subscription and its introductory offer is $195 for the first year, which is more than 60% off the regular price. If the schema synchronization is a feature you need, I invite you to download a free trial of Toad for Oracle.

Watch a video demo: Toad for Oracle How to compare two schemas

Related information

Video: Toad for Oracle - Compare DB environments with multiple schemas

Blog: Compare and Sync Multiple Tables with Toad

More blogs from Clarissa

Have questions, comments or an idea for a new Toad feature?

Head over to the Toad for Oracle forum on Toad World®!  Chat with the 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!