Chat now with support
Chat with Support

Toad for Oracle 12.12 - Getting Started Guide

Table of Contents 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 and Synchronize Multiple Tables

The Toad Compare Multiple Tables wizard makes it simple to compare more than two tables. You simply point Toad the schemas, then synchronize the data in the tables of those two schemas.

Note: This feature will synchronize the data in the compared tables, but there is not an option to compare the data differences first. To compare the data, go to Database | Compare | Data.

To compare and synchronize multiple tables

  1. From the mail Toad menu, select DatabaseCompareCompare Multiple Tables. In the Automation Designer, you can select your app, then double click .
  2. Make selections according to the following descriptions. The descriptions are listed in the order in which they appear in the wizard. Click Next to move from one page to another.

Choose Schemas page

Schemas tab

Select the source and target database connections and schemas that contain the tables that you want to compare.

Specify any database link options as desired.

There are two different techniques of synchronization that this feature can perform. Which one Toad uses depends on what you do on this tab.

  • If the tables to be compared are in the same database, use the same connection to access the source and target schemas. In this case, Toad can synchronize the tables with just two SQL statements per table: a DELETE and a MERGE. All of the work is performed by the database, so the data does not get fetched to your client PC, and you will not run out of memory if you are synchronizing large tables. This method is the faster method.
  • If the source and target schemas are in different databases, select Allow Toad to create a temporary DB Link from Target to Source. In this case, Toad creates a private database link on the target database that points back to the source. This allows Toad to join the tables and perform the DELETE and MERGE operations as if the tables were in the same database. The database link has full TNS information, so there is no need for an entry to exist in the TNSNames.ora file on the target server. Toad drops the link when the synchronization is finished.

    Note: Toad refers to the previous two methods as Comparison by SQL.

  • If you cannot allow a database link to be created, and the schemas are in different databases, then Toad must fetch all of the rows from each table to your PC and then compare them row by row. This is the slowest and most memory-intense option. If possible, use the database link when the schemas are in different databases. Note: Toad refers to this method as Row-by-row comparison.

Options tab

This tab enables you to determine what Toad does when it finds anomalies during the initial comparison.

Option Description
If columns don't match exactly

Select what you want to do if the source and target tables do not have the same number of columns.

 

If a table contains unsupported datatypes Select what you want to do if a table contains an unsupported datatype. See Potential comparison problems for examples.
If any table is to be skipped Select what you want to do when incompatibilities are found during the comparison.
If an error occurs during synchronization

Select what you want to do if there are synchronization errors.

  • Commits are automatic, but you have some control over them with this option:

    Rollback current table and continue directs Toad to issue a commit after each table (except if the synchronization of that table is rolled back due to an error).

    Rollback all tables and stop directs Toad to either commit at the end of the process or roll it back when the first error happens.

    Note: It is not possible to provide the option to commit or do not commit, because DDL occurs at the beginning and end of each compare and synchronization (disable/enable of constraints and possibly triggers, create/drop database link), and therefore commits are implicit in most cases.

  • Triggers By default, triggers on the compared tables are disabled so that they do not cause DML to be performed on child tables that may also be compared and synchronized by Toad. Double operations like this will cause errors. Clear this selection if you need the triggers to fire.

    Choose Tables page

    To compare all of the tables with matching names in your selected schemas, do nothing on this tab. Otherwise, use this page to specify how to match table names, handle table names that do not match or compare a subset of the tables in the selected schemas.

    Option Description

    Match tables automatically

    and

    Table names match exactly

    Both of these options are enabled by default and indicate that you do not want to refine the table selection further.

    Otherwise:

    • To compare a subset of the tables, deselect Match tables automatically.
    • To map table names that do not match, deselect Table names match exactly.
    • Or, deselect both if neither of those criteria apply.
    Table name filter To load only specific tables based on their names, enter standard filter syntax here.

    To match target tables to source tables

    Select Add before table name or Add after table name if the tables in one schema have a different naming convention than tables in the other schema. Examples:

     

    Source has TABLE1, TABLE2, TABLE3

    Target has TBL_TABLE1, TBL_TABLE2, TBL_TABLE3

    You want to compare TABLE1 with TBL_TABLE1, etc.

    Put TBL_ in Add before table name.

     

    Source has TABLE1, TABLE2, TABLE3

    Target has TABLE1_TBL, TABLE2_TBL, TABLE3_TBL

    You want to compare TABLE1 with TABLE1_TBL, etc.

    Put _TBL in Add before table name.

     

    Source has TABLE1, TABLE2, TABLE3

    Target has TBL1, TBL2, TBL3

    You want to compare TABLE1 with TBL1, etc.

    Put TABLE in Search for and TBL in Replace with.

     

    Source has TBL_TABLE1, TBL_TABLE2, TBL_TABLE3

    Target has TABLE1, TABLE2, TABLE3

    You want to compare TBL_TABLE1 with TBL_TABLE1, etc.

    Put TBL_ and leave Replace with blank.

    Reload Click this option to reload the list of tables after applying filters.
    Include

    Select this checkbox for every table that you want to include in the synchronization.

    To map non-identical table names, use the dropdown selector in the Target column to map a target to the source table listed in that row under Source.

    Validate To check the compatibility of the source and target tables for comparison, click this option. See Potential comparison problems for examples.

    Choose Output page

    Option Description
    Sync target tables immediately

    Select this option to start the synchronization the moment you click to start the processing.

    When this option is selected, Array DML is active and selected by default. Array DML means that Toad will apply deletes in batches of up to 1000 rows at a time, and inserts/updates in batches of up to 100 rows at a time. Array DML greatly increases the speed of the synchronization, so you should leave it selected. In this mode, update statements update every column in the rows being synchronized, even if only one column value actually changed. If you do not agree with this methodology, uncheck Array DML. Note: If a large percentage of the rows in a large table need to be synchronized, running the synchronization without Array DML may be very slow compared to using Array DML.

    Create one script...

    Select Create one script for each table or Create one script for all tables and then specify the output folder or file name.

    Compare and Sync page

    Click to start the processing. Toad shows the progress and results of the operations on the tables that you specified.

    The grid shows a row for each table to be compared. Toad makes two passes through the list, first to evaluate the tables for compatibility and to disable foreign keys, and the second to perform the comparison and synchronization. The comparison type is shown:

    • For Comparison by SQL (performed by the database), Toad displays the number of rows that are deleted and merged (inserts and updates).
    • For a Row-by-row comparison (performed by Toad on your PC), this window displays details about the rows that are inserted, deleted, and updated, as well as the number of matching rows (the Matches column). The Time Remaining column shows how much time remains on each step (one step is deletes, the other is insert and update).

    Potential comparison problems

    The following chart shows potential validation problems that can arise from incompatible source and target tables. In this table, Supported in Row By Row Comparisons refers to comparisons performed by Toad on your PC, and Supported in Comparison by SQL refers to comparisons performed by the database.

     

    Compare Schemas

    You can compare entire schemas or selected subsets of schemas from the Schema Browser.

    Notes:

    • You can compare schemas in the Base Edition, but Snapshot files and sync scripts are only available with the DB Admin Module or Toad for Oracle Xpert Edition.
    • Toad also provides an easy to use Compare Schemas wizard. Compare Schemas

    To compare schemas

    1. Select Database | Compare | Schemas.
    2. Complete the fields as necessary. 
      Schemas Tab Description

      Reference Schema (Source)

      Select a schema connection or schema definition file to use as the basis for comparison. If you execute the sync script, the target schema is updated to match the reference source.

      Tip: If you select Create Schema Definition File, you can use variables in the filename. By default, Toad includes the %DATEFILE% and %TIMEFILE% variables, which inserts the current date and time into the filename when it creates the definition file.

      Targets and Output

      Click in the Live Schemas or Def Files field to add a target schema for comparison. You can repeat this step to compare multiple target schemas to the source.

      Tip: Right-click a target to edit it, delete it, or switch it with the reference source.

      Options Tab

      Description

      Object Types to Compare

      Select the object types you want to compare. Reducing the object types reduces the amount of time it takes to complete the comparison.

      Tip: Right-click to select or clear all fields, and to save or recall your settings.

      Object Set Tab

      Description

      Specify Object Set

      Select to determine specific object sets to compare. This lets you limit your comparison even more than the Options tab. Note: If Compare Schemas was opened as the result of the Compare with another object command targeting a multi-object selection, the selected objects are shown here.

      Click to add an object set. If you already have objects loaded, a confirmation dialog ask you if you want to clear the grid before loading the new objects. Click Yes to start over, or No to append the new objects into the grid.

    3. Click to execute, or save or schedule the selections as a Toad action. See Automation Designer Overview in the online help for more information.

      Tip: You can execute actions from the command line. See Execute Actions from the Command Line in the online help for more information.

    4. Review the differences on the Results tab. This tab has a filter that enables you to narrow the focus if needed. Additionally, the objects are grouped by type by default. To remove the grouping, right-click the list tree and deselect Group by Object Type.

      If you want to...

      Do the following:

      Show the sync script for selected objects.

      Select the objects and click .

      View the differences for one object

      Select the objects and click .

      View the differences for another target Select the target in the Target Schema field (only applicable if you selected more than one target for comparison).
      Exclude objects from the next comparison Uncheck any items that you want excluded from the next run of this comparison, and then click (Create Exclusion File). You can view this file on the Options | Misc Options tab, from which you can also specify a different exclusion file for the next comparison.
      Save the results Click or to save to a file or to an Excel spreadsheet.
      View a summary of the differences Click (Show Summary) to view a summary that shows the number of objects that are in the source only, in the target only, and in both source and target with differences between them.
      Rebuild a table Enabled if there are column differences (such as column order). Right-click the table and select Send to Rebuild Table window.
      Redefine a table Enabled for most table differences. Right-click the table and select Send to DBMS Redefinition Wizard.
      Redefine partitions Enabled if there are partitioning differences. Right-click on a partition then select Send to Partitioning Wizard in Space Manager (requires a licensed copy of that software).
    5. To sync the schemas, select the Sync Script tab and click to execute immediately from the Editor or to schedule its execution.

      Caution: Review this script thoroughly before executing it to prevent unintentional data loss.

     

    Compare and Synchronize Schemas

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

    To use the wizard

    1. Go to Database | CompareSchemas.
    2. Select the Schemas tab.
    3. Under Source, select the database connection and schema on which to base the comparison. You can use the actual state of the database or a snapshot.
    4. Under Target, select the database connection and schema (database or snapshot) to compare to the source schema.
    5. Under Compare Object Types, select the types of objects that you want to compare.
    6. Select the Options tab.
    7. Select any additional conditions to qualify and filter the comparison.

      Object Types: Enables further refinement of the comparison for certain object types.

      Storage Clause: Enables the comparison of specific (or all) storage attributes of the objects.

      Script: Specify options for the output script.

      Filter: Specify filter criteria to refine the objects that are selected.

    8. Click Next.
    9. Review the compare settings.
    10. Click Compare.
    11. Review the differences.

      • Click any object in the list to view the DDL that will be issued on the target schema by the synchronization script.
      • Deselect any object that you want to exclude from the synchronization script.
    12. Click Next.
    13. Review the script syntax. To copy the script to store it outside Toad, click Copy to Clipboard.
    14. Click Next.
    15. Select one or more ways in which to deploy the synchronization script:

      Deploy: Specify a local directory in which to store the script file.

      Check into Source Control: Specify the path to a directory where the script is to be stored in the version control system.

      Send to Editor: Open the script in the Toad Editor or further editing. To deploy the script when you click Next, select Execute Immediately.

     

    Compare Multiple Schemas

    This feature is ideal for comparing multiple schemas in one database to the same schemas in another database.

    Note: You can compare multiple schemas in the Base Edition, but Snapshot files and sync scripts are only available with the DB Admin Module or Toad for Oracle Xpert Edition.

    Compare schema-level objects (tables, indexes, views, etc.) in every schema across two databases, without individually identifying each source and target schema.

    To compare multiple schemas

    1. Go to Database | Compare | Multiple Schemas.
    2. Select your source and target databases (these can be the same database).
    3. Click the Schemas tab and specify your selection criteria:
      See the table below for more detail.
    4. Click Load. You can select/deselect listed schemas if you have chosen Manually... in step 3 above.
    5. Click the Options tab; the options are the same as the basic Schema Compare window. Compare Schemas
      • Select the items you want to compare

        Tip: Right-click to select or clear all fields, and to save or recall your settings.

    6. Click the Output tab to specify:
      • Your output folder (files are named based on the schema name).
      • Create difference summary files
      • Create difference detail files
      • Create sync script files
      • Note: Sync script files are created in Toad's Base Editions; running them is available with the DBA Admin Module.
    7. Click the run button, labeled 'Begin Comparison.'
      The Results tab appears, with your selected outputs displayed.
    8. Review differences on the results tabs, depending on the outputs you selected in step 6 above.

    Using the Schemas tab

    In step 3 above, you can ask Toad to automatically match schema names, or manually specify the matching criteria.

    When to match up schemas:

    Option Description

    Automatically, at time of comparison

    If selected:

    • You cannot then select or deselect schemas listed after you click Load.
    • Built-in schemas like SYS, SYSTEM, etc, are automatically excluded, and this list of automatically excluded schemas is configurable
    Manually, before comparison

    If selected:

    • Click in the Target column to change the target schema. (A drop-down arrow appears.)
    • Use alternately-named schemas, as described below under Manipulate source schema....

    How to match up schemas:

    Option Description

    Match schema names exactly

    Once listed, if Automatically, at time of comparison is selected, you can select or deselect schemas to compare.

    Manipulate source schema name to find target schema name

    If selected, you can:

    • Prepend text - For instance, if your source and target schema names are Dev_Employees and Employees.
    • Append text - For instance, if your source and target schema names are Employees_Test and Employees.
    • Search for/Replace with - For instance, if you search for Dev and replace with Prod if your source and target schema names are Dev_Employees and Prod_Employees.

    On the Results tab, you can:

    • Re-compare selected schemas
    • Send selected or all sync scripts to the Editor (with the DB Admin Module or Toad for Oracle Xpert Edition)

    Depending on the outputs you selected in step 6 above, you can also view these tabs:

    • Difference Details (tree view)
    • Difference Summary
    • Sync Script

    The list of schemas that are automatically ignored is in Toad.ini.

    If you want to adjust it, find this line and make your changes:

    MultipleSchemasToIgnore='ANONYMOUS', 'APPQOSSYS', 'CTXSYS', 'DIP', 'DBSNMP', 'EXFSYS', 'FLOWS_FILES', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'OLAPSYS', 'ORACLE_OCM', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', 'OUTLN', 'OWBSYS', 'OWBSYS_AUDIT', 'SI_INFORMTN_SCHEMA', 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'SYS', 'SYSMAN', 'SYSTEM', 'WMSYS', 'XDB', 'XS$NULL'

    The APEX schemas are also automatically ignored. If you don’t want that, find this line and change the ‘1’ to a ‘0’:

    MultipleSchemasIgnoreApex=1

     

    Related Documents

    The document was helpful.

    Select Rating

    I easily found the information I needed.

    Select Rating