Chat now with support
Chat with Support

Toad for Oracle 15.0 - Getting Started Guide

Welcome to Toad Toad Resources Required Privileges Create and Manage Database Connections Editor Basics Schema Browser Basics Data Grid Basics Work with Data Work with Database Objects Work with Code
Write Statements and Scripts Work with the Query Builder Execute Statements and Scripts Save Statements (SQL Recall) Work with PL/SQL Objects Debug Analyze Code Optimize SQL
Customize your Toad Environment Use Other Quest Software Products with Toad

Compare Schemas

The Compare Schemas Wizard provides an easy to use interface for doing comparisons among two or more schemas. Based on your specifications, the wizard generates a script that you can save to a folder on your computer or run immediately to synchronize the two schemas. The wizard also enables you to add or check in the script to source control.

To run the wizard

  1. (If comparing from databases) Connect to the source and target databases that contain the schemas to be compared.

  2. Do either of the following:

    • From the Toad menu, select Database | Compare | Schemas.
    • On the DB Misc tab of the Automation Designer, double-click (Compare Schemas) to add the action to your app, then double-click the action to open the Properties dialog.

  3. The Choose Schemas page is shown. You can compare:

    • One source schema or snapshot file to one or more target schemas or snapshot files
    • Multiple pairs of schemas between a source and target database
    • One or more pairs of snapshot files.
  4. Select one of the following sets of steps for the type of comparison that you want to run.

To compare one source schema/snapshot file to one or more target schemas/snapshot files

  1. Under Source, select Database or Snapshot, then select the connection and schema or snapshot file as appropriate.

  2. Do one of the following under Target:

    To select one target at a time:

    1. Click .

    2. In the Add Schema dialog, select either Database or Snapshot, then select the target connection and schema or snapshot file as appropriate.

    3. Click OK to add the selection to the Connection/File list.
    4. To add another target, click then repeat the steps.

    To select multiple targets at once:

    1. Under Target, click the arrow next to then select Add Multiple Schemas.

    2. Select Same Database to compare different schemas within the same database, or select Different Databases to compare the same schema from different databases.

    3. Do one of the following, depending on what you selected in the previous step:

      • (If you selected Same database) Select the database under Connection, then double-click the schemas under Available Schemas to move them to the Selected Schemas list.

      • (If you selected Different databases) Select the comparison schema under Schema, then double-click the target connections from Available Connections to move them to the Selected Connections list.

    4. Click OK to add the selection to the Connection/File list.
  3. Click Next.
  4. Select options from the Choose Options window. See Choose Options
  5. Click Compare to run the comparison.
  6. See Using the Comparison Results.

To compare multiple pairs of schemas between a source and target database

  1. Under Source, select Database.
  2. Under Source, select the source database under Connection, then select <multiple schemas> from the Schema dropdown.

  3. Under Target, select the target database under Connection.

  4. (Optional) Under Target, use the Schema filter box to match the schemas to a particular pattern. For example, in the screenshot below, I could change the filter to “COMP%” to only compare the schemas that begin with the letters COMP.

    • Select Match schema names automatically to have Toad automatically match the source and target schemas by name when you run the comparison. The match is shown in the lower half of the window. (Note: Built-in schemas like SYS, SYSTEM, ORDSYS, etc, are excluded. Look for “MultipleSchemasToIgnore” in the Toad.ini file if you want to edit the list of schemas that Toad will ignore).
    • Deselect Match schema names automatically to choose the schemas to compare manually. Toad will display the schemas found in the Source and Target Connections, and it will check the ones it finds that exist in both schemas. You can change the Target schema if you want to match a source to a schema by a different name, and you can check/uncheck in the Include column to indicate which schema pairs to compare.
  5. Click Next.
  6. Select options from the Choose Options window. See Choose Options
  7. Click Compare to run the comparison.
  8. See Using the Comparison Results.

To compare one or more pairs of snapshot files

  1. Under Source, select Snapshot then select <Multiple Files> for Snapshot File.

  2. Do one of the following:

    • Specify Files: Click click to add a pair of snapshot files to the comparison. Repeat this step as many times as needed to add all of the source-target snapshot file pairs.
    • Specify folders: Select this option to specify source and target folders that contain the snapshot files. Use this option only if all of your source files are in one folder and all of your target files are in another folder, and the pairs of files to be compared have the same file names.
  3. Click Next.
  4. Select options from the Choose Options window. See Choose Options
  5. Click Compare to run the comparison.
  6. See Using the Comparison Results.

Choose Options

Note: You can right-click to select Check All or Uncheck All and Expand All or Collapse All if needed.

Option

Description

Compare | Object Types

Select the object types that you want to compare. Unchecked items are ignored. Note: You can right-click to select Check All or Uncheck All and Expand All or Collapse All if needed.

Compare | Output

Output of the synchronization script is optional and can be saved later, but for multiple comparisons it is more convenient to configure output than to save the files later.

  • Base Output Folder: Toad creates a subfolder for each compared pair of schemas in the specified folder.
  • Snapshot Files: Theses checkboxes are only enabled when the schema being compared is from a live database, not a snapshot file. Select one or both to have snapshot files saved.

  • Comparison Results:

    Difference details and the difference summary can be output as follows:

    • Select File: to save the results in the folder specified for Base Output Folder.
    • Select Email to email the results instead of being saved to file. To use the email option, you must configure the Email Settings in Toad Options (View | Toad Options).

    The Sync script that Toad produces to synchronize the source and target schemas can be emailed, saved to file, or sent to the source control location that is specified with the Source Control options. The source control system itself must be specified in the Team Coding configuration.

Options | Objects

Select additional comparison criteria for objects that you are comparing.

Options | Storage

Select which storage attributes to compare.

Options | Script

Select the items to include in the synchronization script.

Filters

Further refines the object selection.

Compare Object Names: Compares only objects that have a name that matches (or does not match) the pattern you supply.

Maximum number of differences: Stops the comparison if the specified number of differences is found in a pair of schemas.

Exclude File: Prevents certain objects from appearing in the synchronization script (details on how to create an exclude file are given in the “Compare and Review” section.

Object Set: Lists which objects are to be compared. If no objects of a particular type are listed here, then all objects of that type will be compared. For example, suppose you selected both “Tables” and “Views” in “Object Types”, and in the Object Set grid, you listed three tables. Of all the tables in the schema, only those three will be compared. Because you did not specify any views in the Object Set, all views in the schema will be compared.

Using the Comparison Results

When the initial comparison is complete, the bottom half of the window shows the results for the selected pair of schemas on a series of tabs.

  • Right-click on an object on the Difference Details tab to view additional options.
  • To exclude an object from the synchronization script, uncheck it in the Difference Details list. You can then create an exclusions list by right-clicking in the window, then selecting Create Exclusion File from unchecked items. A file will be produced based on your selection. This file contains further instructions on how to edit it, if needed. To filter based on the exclusion file, click Back to go back to the Options page, then specify the file under Exclude File. You can then run the comparison again with the objects excluded.
  • To find a particular object in the Difference Details, click anywhere within the list and then press CTRL+F on your keyboard to get a filter dialog. Press ESC to undo the filter.

 

Compare Databases

The Compare Databases wizard provides an easy way to compare two or more databases, either live or from a snapshot. Based on your specifications, the wizard generates a script that you can save to a folder on your computer or run immediately to synchronize the two databases.

This feature is only available with the DB Admin Module, which is included in several Toad Editions or can be purchased separately.

To run the wizard

  1. (If comparing from databases) Connect to the source and target databases that you want to compare.

  2. Do either of the following:

    • From the Toad menu, select Database | Compare | Databases.
    • On the DB Misc tab of the Automation Designer, double-click (Compare Databases) to add the action to your app, then double-click the action to open the Properties dialog.

  3. Under Source, select Database or Snapshot, then select the connection or snapshot as appropriate.

  4. Click .
  5. In the Add Schema dialog, select either Database or Snapshot, then select the target database or snapshot file as appropriate.
  6. Click OK to add the selection to the Connection/File list.
  7. To add another target, click then repeat the steps.
  8. Click Next.
  9. Select options from the Choose Options window. See Choose Options
  10. Click Compare to run the comparison.
  11. See Using the Comparison Results.

Choose Options

Note: You can right-click to select Check All or Uncheck All and Expand All or Collapse All if needed.

Option

Description

Compare

Under Object types and Database settings, select the objects and settings that you want to compare and, if applicable, synchronize. Unchecked items are ignored. Note: You can right-click to select Check All or Uncheck All and Expand All or Collapse All if needed.

Output

Output is optional and can be saved later, but for multiple comparisons it is more convenient to configure output than to save the files later.

  • Base Output Folder: Specify a folder to contain the synchronization script that Toad produces and other output.
  • Snapshot Files: Theses checkboxes are only enabled when the schema being compared is from a live database, not a snapshot file. Select one or both to have snapshot files saved.

  • Comparison Results:

    Difference details and the difference summary can be output as follows:

    • Select File: to save the results in the folder specified for Base Output Folder.
    • Select Email to email the results instead of being saved to file. To use the email option, you must configure the Email Settings in Toad Options (View | Toad Options).

    The Sync Script that Toad produces to synchronize the source and target databases can be emailed or saved to file.

Options

Select additional comparison criteria for objects that you are comparing and that you want in the synchronization script.

Note: If you plan to run the synchronization script and you selected User/Role privileges under Object types, make certain Don't synchronize passwords for users and roles remains selected if you do not want the target passwords to be overwritten by the source passwords.

Filters

Further refines the object selection.

Maximum number of differences: Stops the comparison if the specified number of differences between databases is found.

Object Set: Select this option to filter the comparison to a set of specific objects.

  • Click to open a dialog that enables you to specify an object type, schema, and filter rule for that object type. You can then further refine the list by deselecting objects as needed.
  • Click to save this rule to a file as an Object Set for use in later comparisons.
  • Click to load a saved object set to this comparison.
  • To make it easy to load more than one object type, clear Close on rows loaded. It prevents Toad from closing the dialog after your first selection and then prompting to clear the grid when you click again.
  • Select View/edit query before executing to make changes to the query that runs the object selection.

If no objects of a particular type are listed here, then all objects of that type will be compared.

Using the Comparison Results

When the initial comparison is complete, the bottom half of the window shows the results for the selected pair of schemas on a series of tabs.

  • Right-click on an object on the Details tab to view additional options. Tip: Right-click an object in the list, then select Show sync script for selected items to view the DDL that is in the script for the selected object.
  • To exclude an object from the sync script, uncheck it on the Details tab.
  • You can save the selected objects to a file by clicking on the toolbar of the Details tab.
  • Use the Summary tab to view the differences in the two databases, grouped by objects that were compared. You can save this as html by clicking on the toolbar.
  • Use the Sync Script tab to view the DDL changes that will be made to the target database when you run the script. You can save this script to a variety of file types by clicking on the toolbar or by right-clicking within in the tab and then selecting Save. You can also print the script from the right-click menu.

 

 

Rebuild Objects

Toad enables you to view, add, modify, and get information about database objects. You can work with these objects quickly and without having to use SQL commands.

 

About Rebuilding Objects

Use the Rebuild Multiple Objects window to identify objects that need to be rebuilt, and then set criteria to rebuild them simultaneously.

Rebuild Multiple Objects

You can also rebuild one index or table at a time from the Schema Browser, with a simpler interface.

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating