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.
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.
You can compare individual objects to other objects. If the objects are of the same name and type, the comparison produces a synchronization script, which you can edit if needed, and then run to bring the source and target back into synchronization.
You can initiate the comparison from the Schema Browser or from the Database menu.
To compare objects from the Database menu
From the Database menu, select Compare | Single Objects.
To compare individual objects from the Schema Browser
In the Schema Browser, right-click on an object or a pair of objects, then select Compare.
Note: You can also select multiple objects from the Schema Browser for comparison. This is not a single-object comparison, and not covered in this topic. If you do select multiple objects, you are redirected as follows:
To configure and run the comparison
On the Objects tab:
Under Target, select one of the following:
Do one of the following, which will determine the objects that you can choose under Object Type:
On the Options tab:
Click Compare. The results are shown on three tabs:
Compare Columns tab: Shows the columns that were compared. You can add the following to this view:
| Option | Description |
|---|---|
|
Alphabetical |
|
|
Precision/Scale |
Shows and compares the precision and scale of the columns. |
|
Hidden Columns |
Shows and compares hidden columns. |
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 multiple tables
Do one of the following:
(Compare Multiple Tables) to add the action to your app, then double-click the action to open the Properties dialog. 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.
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 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.
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. 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. |
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:
|
| 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. |
| Option | Description |
|---|---|
| Sync target tables immediately |
Select this option to start the synchronization the moment you click 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. |
Click 
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:
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.
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
Do either of the following:
(Compare Schemas) to add the action to your app, then double-click the action to open the Properties dialog. The Choose Schemas page is shown. You can compare:
To compare one source schema/snapshot file to one or more target schemas/snapshot files
Under Source, select Database or Snapshot, then select the connection and schema or snapshot file as appropriate.
Do one of the following under Target:
To select one target at a time:
.
In the Add Schema dialog, select either Database or Snapshot, then select the target connection and schema or snapshot file as appropriate.
then repeat the steps. To select multiple targets at once:
Under Target, click the arrow next to
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.
To compare multiple pairs of schemas between a source and target database
Under Target, select the target database under Connection.
(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.
To compare one or more pairs of snapshot files
Do one of the following:
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.
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.
|
|
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. |
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.