When you are editing table data in the Schema Browser, you can look up foreign keys and enter the associated data into the selected table.
Foreign key lookup will also work when the grid is in Read Only mode. You can disable this feature from the Toad Options | Schema Browser | Data and Grids | Enable FK Lookup.
To lookup foreign keys
Within the lookup window, all foreign key constraints are included. If you want Toad to ignore disabled constraints, see Toad Options | Schema Browser | Data.
You can filter rows by typing or by directly editing the query.
To filter rows by typing
To filter rows by editing the query
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:
Shows and compares the precision and scale of the 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:
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.
|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.
Match tables automatically
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.
|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.|
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.|
|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.
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:
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.