Data Compare for Oracle - 2.1
Worked example: comparing and deploying data - Data Compare for Oracle
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:
- Click here to view 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 do not not 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 are 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:
The Tables tab enables you to 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
DESCRIPTION are the same in both schemas, so the data is displayed in gray text.
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: Using the Find box.
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.
- Ensure 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 deplyment
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:
Was this article helpful?
Thanks for your feedback!
all SQL products
- Compatibility of Red Gate tools in 64-bit environments
- Application has encountered an error and needs to close
- Error message after installing SQL Toolbelt - The description for Event ID ( 1 ) in Source ( nview_info ) cannot be found.
- Changing the temporary directory used by the installer
- Toolbelt Installer "hanging" while "scanning volumes"
- Login failing with "trusted SQL Server connection" error when using RunAs
- Some Red Gate products identified as containing a trojan by Anti-Virus software
- Activation may fail with Unknown Error -1
- Product uses web help although a CHM file is available locally
- Argument exception resulting from missing environment variable
- Check for updates may fail when used through proxies
- 'Unidentified Publisher' error when repairing or uninstalling
- Licensing activates product as standard edition
- Moving Red Gate software products to another machine
- Red Gate tools log locations
- The application UI opening slowly when there is no internet access
all SQL products
- Red Gate product acknowledgements
- Activating your products
- Activating your products
- Red Gate bundle history
- Check for updates
- Troubleshooting Check for Updates errors
- Current versions
- Deactivating your products
- Installing Red Gate products from the .msi file
- Requesting additional activations
- Serial numbers for bundles
- Reactivating using a different serial number
- Extending your trial
- Finding your serial numbers
- Moving a serial number from one computer to another
- No response received for manual activation
- Licensing and activation resources
- Licensing and activation resources
- Troubleshooting licensing and activation errors
- Licensing and activation FAQs
- Red Gate tools log file locations
- Download old versions of products
- Download product prerequisites & utilities
- Support & upgrades
- Upgrading your software
- Upgrading FAQs