Using the Synchronization wizard - Schema Compare for Oracle

When you have selected the objects that you want to synchronize, you can use the Synchronization wizard to create the SQL script that will synchronize the databases.

To start the Synchronization wizard, click  Synchronization Wizard.

There are four possible steps in the wizard:

1. Choose synchronization method.

   Choose to create and save a synchronization script, or synchronize using Schema Compare for Oracle.

2. Review dependencies.

   View a list of any objects that reference, or are referenced by, the objects you have selected to synchronize. You can choose to synchronize these affected objects.

3. Configure script.

   View a list of issues that affect the synchronization, and select actions to update the synchronization script. If there are no synchronization issues, this step is not shown in the wizard.

4. Review script.

   View the synchronization script, review a summary of the synchronization actions, and see information about any warnings.

1. Choose synchronization method

On the first page of the Synchronization wizard you can choose to create and save a synchronization script, or synchronize using Schema Compare for Oracle.

Synchronize using Schema Compare for Oracle

If you want to create a synchronization script to run at the end of the wizard, in the left-hand pane, select Synchronize using Schema Compare for Oracle:

Note that if the target data source is a snapshot, the option to synchronize using Schema Compare for Oracle is not available. For more information, see Synchronizing snapshots.

You can create a snapshot of the target database schemas before synchronization. The snapshot preserves the state of the target before the synchronization, and later can be used as a source, to restore the target to that state.

Create a synchronization script

If you want to create a synchronization script to open in your SQL editor at the end of the wizard, in the left-hand pane, select Create synchronization script:

To change the SQL editor used to open the script, click Change. The Application Options dialog box is displayed, and you can specify a default editor:

Note that if you use Oracle SQL Developer, the encoding settings in Schema Compare for Oracle and Oracle SQL Developer must be compatible. For more information, see Selecting encoding settings.

2. Review dependencies

The Review dependencies page lists any objects that reference, or are referenced by, the objects you have selected to synchronize:

All the objects you selected for synchronization are listed in the left-hand pane. If any of these objects reference, or are referenced by, objects that you included in the comparison but did not select for synchronization, the objects you did not select are listed in the right-hand pane.

For example, if a view references a table, and you did not select the table for synchronization, the table is listed in the right-hand pane.

Additionally, ghost objects may be listed in the right-hand pane. For more information, see Ghost objects.

You can view the SQL creation script for any object in the right-hand pane by clicking its object name in the Objects affected column. The creation script for the selected object in both data sources is displayed, and any differences are highlighted:

By default, Schema Compare for Oracle synchronizes all affected objects.

You are recommended to synchronize all affected objects. If you do not, the synchronization script may fail, or produce unexpected results.

To exclude an affected object from synchronization, clear the Synchronize all affected objects check box, and then clear the check box for the object.

Ghost objects

A ghost object is an object that was not included in the comparison, but references, or is referenced by, an object that was included.

For example, if a table in the comparison references a stored procedure in a schema that you did not include for comparison, the stored procedure is a ghost object.

Schema Compare for Oracle includes ghost objects in the comparison automatically. This can be useful, for example, if you want to synchronize a table that references a stored procedure, but forget to include the stored procedure in the comparison.

Schema Compare for Oracle lists any ghost objects in the Review dependencies page of the Synchronization Wizard.

You are strongly recommended to synchronize all ghost objects. If you do not, the synchronization script may fail, or produce unexpected results.

Note that:

• ghost objects are not displayed in the comparison results
• if you compare schemas with objects which reference, or are referenced by many ghost objects, the comparison may be slow
• if you save a snapshot of the source or target database, ghost objects may be included in the snapshot

Example: Synchronizing a table with dependencies

The following example demonstrates how Schema Compare for Oracle includes ghost objects in the synchronization.

In the example, you have already compared SchemaA in the Dev database to the same schema in the Test database:

TableA is a table in SchemaA in Dev. You have added a new column to TableA, which you want to synchronize to Test:

The new column references a new data type mytype, which you have created in another schema in Dev, SchemaB:

If you select TableA for synchronization, and you run the Synchronization Wizard, mytype appears in the list affected objects on the Review dependencies page.

To synchronize mytype, ensure its check box is selected, and run the synchronization script.

To exclude mytype from the synchronization, clear the Synchronize all affected objects check box, and then clear the check box for mytype before you synchronize.

3. Configure script

If there are any issues with the synchronization that might cause the script to fail, the Configure script page is added to the Synchronization wizard.

This page lists any issues that affect the synchronization, and allows you to select actions to update the synchronization script.

There are two types of issue:

• No value specified for a NOT NULL column
• Data type must be converted

No value specified for a NOT NULL column

If no default value is specified for a column with a NOT NULL constraint, you can select one of the following actions:

• Allow NULL value

  The column is altered to allow NULL values.

• Specify custom value

  A value you specify is used as the default for the column. To specify a value, in the Value column for the issue, type any constant that is valid for a default expression, or any valid SQL expression:

  

• Ignore issue

  If you select this action and run the synchronization script, synchronization will fail. You are strongly recommended to review and edit the synchronization script before running it on your target database.

Data type must be converted

If the data type for a column must be converted in the synchronization, you can select one of the following actions:

• Specify custom value

  A value you specify is used to convert the data type for the column. To specify a value, in the Value column for the issue, type any valid SQL expression for an INSERT INTO select statement.

• Ignore issue

  If you select this action and run the synchronization script, synchronization will fail. You are strongly recommended to review and edit the synchronization script before running it on your target database.

When you have selected an action for each issue, click Next to review the synchronization script.

4. Review script

The final page of the wizard displays information about the synchronization:

There are three tabs:

• Synchronization script shows the script to synchronize the data sources.

  You can search the script, save it, or copy it to the clipboard.

• Summary shows a synopsis of the actions in the synchronization script.

  You can view the summary grouped by the objects affected, by the type of modification, or by the order in which the script modifies the target.

• Warnings shows a list of any warnings about unexpected behavior that may occur when you synchronize the databases.

  For more information, see Warnings.

When you have reviewed the script, to synchronize the databases click Synchronize Now.