Nov 12, 2020 10:21:22 AM by Clarisa Maman Orfali
Suppose we are developing a web application using the Oracle database as our data storage and we find ourselves:
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.
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.
Figure 4. Toad Choose Schemas – Source and Target
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.
Figure 11. Toad Filters screen
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.
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.
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:
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.
Try Toad free for 30 days.
Already in a trial? Talk to sales or buy now online.
Already a loyal fan of Toad for Oracle? Renew now.
Head over to the Toad for Oracle forum on Toad World®! Chat with the Toad developers, and lots of experienced users.
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!
Written by Clarisa Maman Orfali
Clarisa is from Argentina. She is Founder and Director at ClarTech Solutions, Inc. a company dedicated to the development, consultancy and training in Information Technologies located in Southern California in the United States.
She is a Systems Engineer with more than 18 years of experience in systems development. She was introduced in Oracle Technologies in 2009, a few years later she discovered the great power of development with Oracle Application Express (APEX) and has specialized since then in that area.
She is also Co-Founder of the Oracle Users Group of Argentina (AROUG) and founder of Oracle APEX Latin America meetup, has also actively participated in the organization and as Presenter of big and popular events such as the OTN Tour and the Oracle APEX Tour of Latin America. In 2014 was recognized by the Oracle Corporation with the Oracle ACE Award. Then, in March 2018, she was promoted to Oracle ACE Director.
She is also the author of the first three books in Spanish about Oracle APEX: