Chat now with support
Chat with Support

Toad for Oracle 2024 R2 Common Documents - 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

Look Up Foreign Keys

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

  1. In the Schema Browser, click the Tables tab.
  2. Select the table you want to edit and in the details pane click the Data tab.
  3. Click in a column that has foreign keys. Click again to activate Editing mode. A drill down button displays.
  4. Click to display the rows in the referential table.
  5. To enter data in appropriate columns in the data grid, select a row and click OK.

The Foreign Key Lookup Window

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

  1. Make sure the Filter by box in the lower left is selected.
  2. Put your cursor in one of the displayed columns. You can now filter by typing (if you type "ab" it will filter out rows that do not begin with "ab").

To filter rows by editing the query

  1. Click Edit Query.
  2. Edit the query within the editing window.
  3. Specify variables by clicking the Variable button and entering variable information.
  4. Check that your query syntax is accurate by clicking the Check button.
  5. Click OK to run your new query and filter the lookup grid.

 

Compare 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.

 

Compare Individual Objects

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:

  • If you select more than two objects not owned by a schema, such as users, tablespaces, or profiles, the comparison is directed to the Compare Databases window, which is filtered to only those objects on the Object Set tab. See Compare Databases for more information. Compare Databases
  • If you select more than two schema-related objects, such as tables, views, or packages, the comparison is directed to the Compare Schemas window, which is filtered only to those objects on the Object Set tab. Compare Schemas

To configure and run the comparison

  1. On the Objects tab:

    • Select the source and target database connection.

    • Under Target, select one of the following:

      • Live Object to compare the source object to the actual target object in the database.
      • Text File to compare the source object to a script file of the target object.

    • Do one of the following, which will determine the objects that you can choose under Object Type:

      • To compare schema objects, select Schema, then select a schema for the source and target.
      • To compare non-schema database objects, clear the Schema check box.
    • Select from Object Type.
    • Select from Object Name.

  2. On the Options tab:

    • For Schema Objects: select any additional schema objects that are associated with the selected objects that you want to include in the comparison.
    • For Tables:
      • Select whether or not to reorder the target table with DBMS_REDEFINITION. This package from Oracle enables the redefinition when the table is accepting DML changes.
      • Include Triggers—Select to include the triggers on the table.

  3. Click Compare. The results are shown on three tabs:

    • Compare Scripts tab: Shows the source and target DDL that was used to compare the objects in the differences viewer.

    • Compare Columns tab: Shows the columns that were compared. You can add the following to this view:

      Option Description

      Alphabetical

      Arranges columns alphabetically before comparing.

      Precision/Scale

      Shows and compares the precision and scale of the columns.

      Hidden Columns

      Shows and compares hidden columns.

    • Sync Script tab: Shows the sync script that Toad generated from the comparison. This tab is only available if the objects chosen have the same name, and are in different schemas. Using the toolbar options, you can copy the script to the clipboard, open it in the Editor to make changes, or save it to a file.

 

Compare 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 multiple tables

  1. Do one of the following:

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

  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.

     

    Related Documents

    The document was helpful.

    Select Rating

    I easily found the information I needed.

    Select Rating