Worked example: Comparing and synchronizing database schemas - Schema Compare for Oracle

This worked example demonstrates a basic comparison and synchronization of two schemas in an Oracle database.

In the example, The Wooly Widget Company has an Oracle database running on a live web server. This database contains many tables, views, indexes, packages, and other objects. The Wooly Widget Company's development team have been working on an upgrade to to their website. As part of this upgrade, they have made a number of changes to the structure of the development schema. They need to transfer the changes from the development schema to the test schema. This worked example uses Schema Compare for Oracle to transfer these changes.

The example has four steps:

1. Set up the schemas.

   Create the example database schemas on your Oracle server.

2. Set up the comparison.

   Specify the schemas you want to compare.

3. Select objects to synchronize.

   Review the comparison results and select the objects you want to synchronize.

4. Synchronize the schemas.

   Create and run a synchronization script.

1. Set up the schemas

The worked example uses the WIDGETDEV and WIDGETTEST schemas:

• WIDGETDEV is the development schema
• WIDGETTEST is the test schema

To create the schemas on your Oracle server:

1. Click here to view the SQL creation script for the schemas.
2. Copy this script, paste it into your SQL editor, and run it on any database you choose.

   The schemas are created.

2. Set up the comparison

1. Start Schema Compare for Oracle if it is not already running.

   The Project Configuration dialog box is displayed showing your most recent project:

   

   In this example, we will create a new project.

2. Click Cancel to close the dialog box.
3. On the toolbar, click New Project.

   A new Project Configuration dialog box is displayed.

4. In the shaded upper pane, ensure both Source and Target are set to Database.

   In this example, we are comparing schemas from a live database; you can also compare schemas from saved snapshots. For more information, see Working with snapshots.

5. Select a connection method to connect to the database on which you set up the schemas:

• If you want to connect using aliases in your tnsnames.ora configuration file, under Source, select the TNS Connection tab.
• If you do not not have Oracle client software installed, or cannot use a tnsnames.ora file, select the Manual Connection tab.

1. Enter connection details for the database on which you set up the schemas.
2. In the User name box, type WIDGETDEV, and in the Password box, type password.
3. To set the target database to the same as the source, click .

   The connection details you entered for the source database are automatically entered for the target.

4. Under Target, in the User name box, type WIDGETTEST, and in the Password box, type password.
5. Under Source, in the Schemas box, select the WIDGETDEV schema.
6. Under Target, in the Schemas box, select the WIDGETTEST schema.

   In this example, we are comparing only one source schema and one target schema. Schema Compare for Oracle allows you to compare as many schemas as you like. For more information, see Selecting schemas to compare.

7. Click Compare Now.

   Schema Compare for Oracle displays a message dialog box that shows the progress of the comparison.

8. When the comparison is complete, to close the message box, click OK.

3. Select objects to synchronize

The results of the comparison are displayed:

The results are grouped by:

To view the objects in a group, click :

To display a side-by-side, color-coded listing of the differences in the object creation scripts, click an object in the upper pane. The SQL Differences pane is displayed. This example shows the WIDGETPRICES table:

For more information, see:

• Viewing the comparison results
• Viewing the SQL differences

To select objects for synchronization, use the check boxes in the middle of the results.

In this example, we will synchronize all objects that are different:

1. To the left-hand side of the comparison results, in the Filter pane, ensure the default filter All object types included is selected.

   The filter defines which objects are displayed. When you use the filter to exclude an object, it is removed from the Results pane and cannot be selected for synchronization.

   For more information, see Using the filter.

2. To select all objects for synchronization, in the central column of the Results pane, select the top level check box:

   

   All objects are selected, and the  Synchronization Wizard button becomes available.

3. Click  Synchronization Wizard.

4. Synchronize the schemas

The Synchronization wizard creates the SQL script to make the objects you selected in the comparison results identical.

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

Choose synchronization method

In this example, we will synchronize using Schema Compare for Oracle:

1. On the left side of the wizard, select Synchronize using Schema Compare for Oracle, if is not already selected.
2. Clear the Create snapshot of target before synchronization check box.

   In this example, we will not create a snapshot before we synchronize. For more information, see Backing up before synchronization.

3. Ensure the Recompare after synchronization and the Save copy of the synchronization script check boxes are selected.

   In this example, we will run the script from Schema Compare for Oracle, and compare the databases afterwards to see that the synchronization has been successful. We will also save a copy of the synchronization script.

4. Click Next.

Review dependencies

The Review dependencies page of the wizard lists any objects that you did not select for synchronization that reference, or are referenced by, objects you did select for synchronization:

All the objects you selected for synchronization are listed in the left-hand pane, and any affected objects are listed in the right hand pane. For more information, see Review dependencies.

In this example there are no affected objects.

Review script

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

There are three tabs on the Review script page:

• Synchronization script displays the script Schema Compare for Oracle has created to synchronize the data sources.

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

• Summary displays 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 displays a list of any warnings about unexpected behavior that may occur when you synchronize the databases.

  For more information, see Warnings.

  In this example there are no warnings.

Performing the synchronization

When you have reviewed the script, synchronize the schemas:

1. Click Synchronize Now.
2. A confirmation dialog box is displayed; to continue, click Synchronize Now.
3. Schema Compare for Oracle displays a message dialog box that shows the progress of the synchronization.

   When the synchronization is complete, click OK to close the message box.

Schema Compare for Oracle then re-compares the databases. The results are shown in the main window. In this example, all objects are shown to be identical, confirming that the synchronization has been a success: