Toad for Oracle 15.0 - Getting Started Guide

Copy Data to Another Schema

You can quickly copy data from one or multiple tables to the same table or tables in another schema or database. Toad builds insert statements that use array binding in the variables to copy the data, or if you prefer, create masked data. If you set the array size to 500, then 500 rows are inserted with a single insert statement. The array size is adjustable.

Note: Toad copies data from one schema to another between tables that have the same table name. The tables must exist prior to running this command.

To copy data to another schema

1. Do one of the following:

   • In the Schema Browser, right-click one or more tables, then select Copy data to another schema.
   • On the Import/Export tab of the Automation Designer, double-click (Copy Table Data) to add the action to your app, then double-click the action to open the Properties dialog.
2. Choose from the menu.
3. Use the Source/Dest and Options tab to select destination connection, schema, and options.

4. Use the Tables, Where Clauses, Data Masking tab to select tables, add and test a WHERE clause, and specify data masking.

   Tip: You can check your WHERE clauses by clicking (Test Where Clauses).

Compare Data

Use the Compare Data wizard to compare data between tables that are within different schemas or iin different databases, such as the same tables in production and test environments. Toad then generates a synchronization script that you can run as-is, save as a file, or send to the Editor to be refined.

To run the Compare Data wizard

1. From the Database menu, select Compare | Data.
2. Complete the Choose Tables and Select Columns pages (see the following descriptions).
3. Click Next to run the comparison. Toad displays the results of the comparison and then provides options for generating the comparison script. See Results Output.

Choose Tables page

Use the tabs of this page to specify the comparison objects and options.

Tables tab

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

2. Specify database link options as needed:

   • If your first data source is remote, select an existing DB Link.
   • If your first data source is local, leave this box blank.
3. Select the object type, owner, and objects name. Click the > arrow to copy the name to the Target side if the source and target selections are the same.
4. (Optional) Add a WHERE clause to further refine the object selection. Click the > arrow to copy the clause to the Target side.
5. (Optional) To make the current target the source (switch sides) click (Switch Source and Target).

Options tab

1. Click Optimizer Hints to specify the type of hint to use. When parallel hint is selected, you can specify the degree of parallelism.

2. (Optional) Click Sort Area Size to change the current sort area size and the one that Toad goes back to when the window closes. This only affects comparisons that do not go through a Database Link.

Select Columns page

Select the columns that you want to include in the comparison. Toad notifies you of discrepancies it finds:

• it does not detect a key column
• a column cannot be compared because it does not exist in both tables
• the data types cannot be compared

Toad handles primary keys as follows:

• If a table has a primary key, Toad finds it automatically and identifies it under the PK column in the grid.
• If no primary key is found, you can select column(s) under the PK column that can be used as a primary key.
• With the primary key identified (automatically or manually), Toad will use those columns to identify whether a row is in both tables, but with differences.
• If no primary key is identified, Toad has no way of identifying a row except by using all of the columns, and rows are either matches, or in one table, or the other.

If structural differences cannot be resolved to enable valid comparison, you can compare the objects themselves. Click (Compare Objects) at the bottom of the page.

Results Output

1. Select what you want to do with rows that match: Count them, display them, or ignore them.

   Option	Description
   Row Count(Match | (Differ)	Displays the total number of rows that were compared. Match means the number of rows in each table is the same. Differ means there is a difference in the row count between source and target tables.
   Source Only and Target Only	These tabs have the same format and show the number of rows that exist only on the source and only on the target.
   Differences	Displays the values of each compared source and target row and highlights differences. This tab displays: Rows from the source dataset in gray Rows from the Target dataset in white Column values that differ between source and target are highlighted in yellow.
   Matches	This result is displayed if you selected Display as the way to handle rows that match. The row values are shown, which reflect their state in both source and target.

2. Do one of the following:

   • Click Compare to run the comparison again.

   • Select from Sync options: Synchronize the data between the tables, send the synchronization script to the Editor, or save the synchronization script to a file. If you choose to synchronize the data, you are prompted whether to commit or roll back the changes.

     Notes: 

     • If you simply close the synchronize window, rather than selecting Commit or Rollback, Toad displays these icons for you to make that selection: .
     • Toad does an automatic savepoint at the beginning of synchronization. If user cancels the synchronization, Toad rolls back to the savepoint.

Review Differences

From the last three windows of the Compare Data wizard you are now ready to view the differences between your data sources.

• The first window reviews rows in Source 1 that are not in Source 2.
• The second window reviews rows in Source 2 that are not in Source 1.
• The last window reviews all differences.

You must run the SQL code for each window as described below.

To make dataset editable

On the Review Differences page, select the Editable Dataset checkbox.

Note: In some editions of Toad, you can delete rows from one table, and insert them into the other directly in the grid.

To review rows

1. Perform any desired optional steps:
   • Click the View/Edit SQL button to view or edit the SQL used to compare differences. You can make changes in the Edit SQL dialog box.
   • Click Check to verify that the query parses correctly.
   • Click OK to apply changes to your query.
   • Click Execute to find differences in the columns you want to compare.

To delete selected rows

1. Select the rows you want to delete.
2. Right-click and select Delete Selected Rows.

To delete all rows

Right-click and select Delete All Rows.

Compare Data Duplicates

Use this dialog box to view record duplicates based on user input.

To view record duplicates

1. Select Database | Compare | Data Duplicates
2. Select the Owner, Object Type and Object from the drop-down lists. A list of columns is displayed below.
   Now, you can do any of the following:

   To find this:	Do this:
   Find duplicates on all columns	Select Find duplicates on all columns. Do not select any columns in the list.
   Find duplicates on just selected columns	Select Find dupes of selected columns. Select one or more columns in the column list.
   On the Duplicate Data tabs, an additional column called Occurences is added to the end of the grid to display the number of resulting duplicates.

To edit duplicate data

1. From the Table Data Duplicates window, select Owner and Table from the drop-down lists.
2. Click the Duplicate Data (Editable) tab.
3. Click the cell you want to edit and make your changes.
4. Click on the toolbar.