How to Compare Two Schemas in Oracle Using Toad
How to compare two database schemas in Oracle using Toad®
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. Make sure to read my second blog in this series,How to compare multiple database schemas in Oracle using Toad . 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 In this example, we are going to compare two database schemas as shown in Figure 2. We will compare the HRDEV schema of the development environment (Source) and the HRPROD schema of the production environment (Target) to show the changes in each of the schemas, using a different connection. Figure 5. Toad Choose Options Screen – Compare à Object Types 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 screenUnder 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. 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 CodeLooking 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. 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 . Blog: How to compare multiple database schemas in Oracle using Toad . Video: Toad for Oracle - Compare DB environments with multiple schemas Blog:Getting to Know the Toad for Oracle Schema Browser Blog: Compare and Sync Multiple Tables with Toad More blogs from Clarissa 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! Are you a developer? Learn to compare two Oracle database schemas using Quest® Toad® for Oracle.
Step by step: how to compare two database schemas
Step 1: The Compare Schemas tool is found in the main menu Database > 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 database schemas. Step 2: In the first tab, click Choose Schemas
Figure 2. Toad Compare Schemas, Choose Schemas
We need to select the Source, either by selecting from a Database or from a Snapshot. Then we select the connection from the source schema and finally we select the target schema, either with the same connection or using a different connection. Step 3: Delete Target Schema
Figure 3. Toad Choose Schemas – Delete Target Schema In this case we need to delete the current target schema to replace it with the HRPROD schema using a different connection, for this action click on the round red button to delete selected target schemas, as shown in Figure 3.
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:
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.
Step 6: Click the Run button and wait for the results as shown in Figure 12.
Figure 12. Toad Compare Schemas – Difference Details tabLet 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 14. Toad Compare Schemas – Difference Summary tabFinally, 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.
Receiving email with the results of the schema comparison
Conclusion
Watch a video demo: Toad for Oracle How to compare two database schemas
Related information
Have questions, comments or an idea for a new Toad feature?
Help your colleagues
Tags: Toad for Oracle Toad for Oracle Videos
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.
How to Compare Two Schemas in Oracle Using Toad
Source: https://blog.toadworld.com/how-to-compare-two-schemas-in-oracle-using-toad