Schema Compare for Oracle 4

Using the Deployment Wizard

When you've selected the objects you want to deploy, you can use the Deployment Wizard to create the SQL script that will deploy the changes.
To start the Deployment Wizard, click Deployment Wizard.

There are four possible steps in the wizard:

  1. Choose deployment method 
    Choose to create and save a deployment script, or deploy 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 deploy. You can choose to deploy these affected objects.
  3. Configure script
    View a list of issues that affect the deployment, and select actions to update the deployment script. If there are no deployment issues, this step is not shown in the wizard.
  4. Review script
    View the deployment script, review a summary of the deployment actions, and see information about any warnings.

1. Choose deployment method

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

Deploy using Schema Compare for Oracle

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

If the target data source is a snapshot, the option to deploy using Schema Compare for Oracle is not available. For more information, see Deploying snapshots.

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

Create a deployment script

If you want to create a deployment script to open in your SQL editor at the end of the wizard, in the left-hand pane, select Create deployment 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:

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 deploy:

All the objects you selected for deployment 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 deployment, 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 deployment, the table is listed in the right-hand pane.

Additionally, ghost objects may be listed in the right-hand pane.

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 deploys all affected objects.

We recommended you deploy all affected objects. If you do not, the deployment script may fail, or produce unexpected results.

To exclude an affected object from deployment, clear the Deploy 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 didn't 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 deploy 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 Deployment Wizard.

We recommend you deploy all ghost objects. If you don't, the deployment script may fail or produce unexpected results.

  • Ghost objects aren't 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: deploying a table with dependencies

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

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 deploy to Test:

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

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

To deploy mytype, ensure its check box is selected, and run the deployment script.

To exclude mytype from the deployment, clear the Deploy all affected objects check box, and then clear the check box for mytype before you deploy.

3. Configure script

If there are any issues with the deployment that might cause the script to fail, the Configure script page is added to the Deployment Wizard.

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

There are two types of issue:

  • No value specified for a NOT NULL column
  • datatype 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 deployment script, the deployment will fail. You are strongly recommended to review and edit the deployment script before running it on your target database.

datatype must be converted

If the datatype for a column must be converted in the deployment, you can select one of the following actions:

  • Specify custom value
    A value you specify is used to convert the datatype 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 deployment script, the deployment will fail. You are strongly recommended to review and edit the deployment script before running it on your target database.

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

4. Review script

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

There are three tabs:

  • Deployment script shows the script to deploy 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 deployment 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 deploy the databases.
    For more information, see Warnings.

When you have reviewed the script, to deploy the databases click Deploy Now.


Didn't find what you were looking for?