Worked example - comparing and deploying data
Published 26 July 2017
This worked example demonstrates a basic comparison and deployment of data in two schemas in an Oracle database.
In the example, The Whacky Widget Company has an Oracle database running on a live web server. This database contains a number of tables, views, stored procedures, and other database objects. The Whacky Widget Company's development team has been working on an upgrade to their website. As part of this upgrade, they have made a number of changes to the development database, which need to be deployed to the production database.
The example has four steps:
- Set up the comparison
Create the example databases and specify the data sources you want to compare.
- Select data to deploy
Review the comparison results and select the rows you want to deploy.
- Deploy the data
Create and run a deployment script.
1. Set up the comparison
The worked example compares data in the ODC_WIDGETDEV and ODC_WIDGETLIVE schemas:
- ODC_WIDGETDEV is the development schema
- ODC_WIDGETLIVE is the production schema
To create the schemas on your Oracle server:
Download the SQL creation script for the schemas.
- Copy this script, paste it into your SQL editor, and run it on any database you choose.
The schemas are created and populated with data.
Specify the data sources
- Start Data 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.
- Click Cancel to close the dialog box.
- On the toolbar, click New Project.
- A new Project Configuration dialog box is displayed:
- 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 don't have Oracle client software installed, or cannot use a tnsnames.ora file, select the Manual Connection tab.
- Enter connection details for the database on which you set up the schemas.
- In the User name box, type ODC_WIDGETDEV, and in the Password box, type password.
- 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.
- Under Target, in the User name box, type ODC_WIDGETLIVE, and in the Password box, type password.
- Under Source, in the Schemas box, select the ODC_WIDGETDEV schema.
Under Target, in the Schemas box, select the ODC_WIDGETLIVE schema.In this example, we're comparing only one source schema and one target schema. Data Compare for Oracle allows you to compare as many schemas as you like. For more information, see Selecting schemas to compare.
Select tables to compare
- To specify the tables you want to compare, click the Tables tab:
In the Tables tab, you can specify:
- the tables you want to compare
- which specific columns you want to compare
- the comparison key Data Compare for Oracle uses to match rows in the two databases. In our example, all the tables are selected with default comparison keys.
- Click Compare Now.
Data Compare for Oracle displays a message dialog box that shows the progress of the comparison.
- When the comparison is complete, to close the message box, click OK.
2. Select data to deploy
The results of the comparison are displayed:
In this example, the results are grouped by:
tables or views with differences in their rows
tables or views with identical rows only
To view the objects in a group, click:
The upper (Results) pane shows how many rows of each type exist for each table. For example, the table WIDGETPRICES contains:
- three rows that exist in ODC_WIDGETDEV but not in ODC_WIDGETLIVE
- one row that exists in both schemas but has different values
- no rows that exist in ODC_WIDGETLIVE but not in ODC_WIDGETDEV.
The Identical column shows that there are two rows that are identical.
Viewing row differences
To view detailed information about the data in a table, click on it in the Results pane. The lower (Row Differences) pane is displayed.
Click the table WIDGETDESCRIPTIONS to see its row differences:
In this example WIDGETID is the comparison key , used by Data Compare for Oracle to match rows in the two databases.
For the row where WIDGETID = 2, the values for SHORTDESCRIPTION and DESCRIPTION are the same in both schemas, so the data is displayed in gray text.
The PICTURE values are different so they are displayed with the darker shaded background.
To view a specific value in a row double-click it, or right-click and select Show Viewer. Binary Data values are displayed as links. Click the Binary Data link for the row where WIDGETID = 2:
Searching the data
You can also search the values in the Row Differences pane using the Find box.
To search for a value, right-click any value in the Row differences pane, and click Find:
For a short example video, see Searching the data.
Selecting rows to deploy
To select objects for deployment, use the check boxes in each pane. In this example, we will deploy all objects:
- If all objects are not already selected, on the right-hand side of the top grouping bar, in the Actions menu, click Include All:
All objects are selected and the Deployment Wizard button becomes available.
- Click Deployment Wizard.
3. Deploy the data
The Deployment wizard creates the SQL script to make the data you selected in the comparison results identical.
On the first page of the wizard you can choose to create and save a deployment script, or perform the deployment using Data Compare for Oracle.
Choose deployment method
In this example, we will deploy using Data Compare for Oracle:
- On the left side of the wizard, select Deploy using Data Compare for Oracle, if is not already selected.
- Make sure the Recompare after deployment check box is selected.
In this example, we will run the script from Data Compare for Oracle, and compare the databases afterwards to see that the deployment has been successful.
- Click Next.
The second page of the wizard displays information about the deployment:
There are three tabs on the Review script page:
- Deployment script displays the script Data Compare for Oracle has created to deploy the data.
You can search the script, save it, or copy it to the clipboard.
- Summary displays 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 displays a list of any warnings about unexpected behavior that may occur when you deploy the data.
In this example there are no warnings.
Performing the deployment
When you have reviewed the script, deploy the data:
- Click Deploy Now.
- A confirmation dialog box is displayed; to continue, click Deploy Now.
- Data Compare for Oracle displays a message dialog box that shows the progress of the deployment. When the deployment is complete, click OK to close the message box.
Data 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 deployment has been a success: