Comparing Schema Differences

Probably the most challenging DBA task is to maintain database structural integrity and consistency. The DBA must always be able to guarantee that production has not changed, and know how development differs from test and test differs from production. And the devil is in the details (the same as it was with schema generation). In the old days when there were just tables, indexes, and views, DBAs simply had a script to generate the differences via the online data dictionary. Of course nowadays there are so many convoluted object types and interdependencies among them that you can't use simple, home-grown scripts any more. So some DBAs will attempt to use data modeling tools like Quest Software's QDesigner or CA's ERwin, but these tools often don't support the more physical aspects of DDL such as tablespaces and partitions. So what's the poor DBA to do? Again TOAD comes to the rescue with the perfect solution with the Compare Schemas screen. Plus this screen can be run from the command line with e-mail notification capabilities. So TOAD can now fully automate your entire schema comparison process via the Windows scheduler.

The Compare Schemas screen is located on the mainmenu at DBACompare Schemas and is shown in Figure 6.44. It's just a simple three-step process to successfully compare even the most complex schemas. First, you visit the Schemas tab and select both your reference and comparison schemas (which are just the source and target, respectively). There is also a handy switch button for reversing the selection, as it's very easy to realize when you view the output that you've chosen them backwards . Also note that you can use TOAD schema definition files as both the source and target. As was previously documented for the Generate Schema Script screen, a TOAD schema definition file is a proprietary format for containing an offline data dictionary and usable by several other TOAD screens.

Figure 6.44. TOAD Compare Schemas ”Source and Target.

Next you visit the Options tab, which itself has two tabs under it. In the Options tab's Object Types to Compare tab shown in Figure 6.45, you can select what kinds of database objects you want to compare and the filename for the generated synchronization script. Also note that this screen offers context menu options for Select All and Select None. This may not have been readily apparent because this is not really a Windows norm.

Figure 6.45. TOAD Compare Schemas ”Object Types.

Then you visit the Options tab's Options tab shown in Figure 6.46, which enables you to select the comparison process control options. For example, you can choose to ignore storage clauses because the development database is sized differently than production and you know this. All but two of these are fairly self-explanatory and require no explanation. The Only Compare Object Names Like field lets you filter using simple Oracle pattern matches (for example, LIKE '%X%' ). And the Stop When # Differences Exceeds check box permits you to define after how many differences the comparison process ends. This is very handy when you have lots of differences and are willing to capitulate (that is, stop) after a certain threshold.

Figure 6.46. TOAD Compare Schemas ” comparison options.

Now you click the Compare button and wait for the results as shown in Figure 6.47. Note that you actually have three results tabs from which to choose. The Results (Interactive) tab permits you to easily view and navigate all the individual difference results, plus it offers the ability to view the SQL required to synchronize just a selected difference as shown in Figure 6.48. Looking again at Figure 6.47, we see that the Results (Interactive) tab presents three major difference categorizations: objects in both schemas that differ, objects in source but not in target, and objects in target but not in source. Opening up the objects in both schemas, but which differ node, as shown in Figure 6.49 exposes the myriad of differences between the development and test databases that were compared. TOAD understands and can compare all major Oracle 8i and 9i features, so you can get a boatload of differences. The Results (RTF) tab shows the same information in a rich text format appropriate for printing and the Results (Summary) tab merely displays a high-level synopsis of the differences found. Most TOAD users find the Results (Interactive) tab to be the preferred interface for examining and resolving the database differences.

Figure 6.47. TOAD Compare Schemas ”comparison results.

Figure 6.48. TOAD Compare Schemas ”individual sync code.

Figure 6.49. TOAD Compare Schemas ”details of the differences.

Finally, you can utilize the Sync Script tab to see all the generated DDL code to synchronize the source and target. You can save the script to a file, print it, load it into the TOAD SQL editor, or just execute it. Note that these scripts can be huge, so the recommendation is to save it to a file and then use TOAD's SQL Editor to run the statements "piecemeal." That way you can control and monitor the entire process. To just blindly execute a huge script that will be altering your database is not very good DBA practice. So take care and proceed with caution. An example synchronization script is shown in Figure 6.50.

Figure 6.50. TOAD Compare Schemas ”Sync Script.

To receive the Compare Schemas comparison results via e-mail is a simple process. You merely visit the TOAD Options category for Email Settings and define all the appropriate information as depicted in Figure 6.51.

Figure 6.51. TOAD E-Mail Options for Compare Schemas.

It would be ideal to have this report waiting for you each morning in your e-mail inbox so that you can start your day by checking the schema comparison statuses of all your databases. You can schedule this report to run on your Windows machine via the Microsoft Task Scheduler as shown in Figure 6.52. For Windows 2000, this is located at StartSettingsControl PanelScheduled TasksAdd Scheduled Task.

Figure 6.52. Windows scheduling of TOAD Compare Schemas.

Note that you're simply scheduling Windows on your PC to run TOAD in command-line mode, where you provide the connection information and a compare schemas (COMP) command text file. To produce that file is very simple. You merely need to open the screen, make all your selections, and then choose the Save All Settings to File toolbar icon. The generated file will contain everything necessary to start TOAD, connect to the database, and then run this screen. The generated command file will also contain comments on some commands you can add to tailor its behavior (for example, to close the screen and also shut down when the process is complete).