Comparing Two Oracle Schemas and Deploying Changes
Schema Compare for Oracle has been designed to provide a fast and accurate way to compare and update instances of your database. The worked example below will take you through the process of comparing two schemas, identifying differences, deploying changes, and verifying that the two environments are in sync after migration.
Red Gate's Oracle Schema Compare is by far the best schema comparison tool on the market; It now takes just 45 seconds to compare 6,000 objects in different instances of my database, this used to take 45 minutes in Oracle SQL Developer!David Cardona, Technical Services Manager, Rostima
Connecting to your Oracle databases
When you run Schema Compare for Oracle, you will be presented with a screen to connect to your databases. Figure 1 shows a connection to a single Oracle database with two different schemas. You can compare schemas on the same database or connect to two different databases. In this example imagine you’re looking to move any changes that you think are relevant from your development schema (WIDGETDEV) into your test environment (WIDGETTEST).
Figure 1: Connect Schema Compare for Oracle to your Oracle databases
How to Compare Two Oracle Schemas
When you have connected to your databases, Schema Compare for Oracle goes off and retrieves the schema information from both of those instances, builds up a picture of all the objects that are in the schemas and identifies any differences.
Figure 2: Compare two Oracle schemas and see differences highlighted side-by-side in Schema Compare for Oracle
At the top of the screen in Figure 2 you can see a summary of the states of the two different instances. On the left is the development schema and on the right is the schema in the test environment, and you can see that there are a number of objects — a view, some tables, a trigger and a package — that exist in both instances that are different.
If you click on an object you can see the DDL for that object in both environments in the bottom window (Figure 3). Any differences are colour coded, line numbered, and highlighted. For example, there’s a column called ‘DATEVALIDFROM’ which only exists in the development environment (WIDGETDEV) for the selected table (WIDGETPRICES). The side-by-side diff view makes short work of identifying differences in Oracle schemas.
Figure 3: Highlighted differences in the DDL for a selected schema object
Selecting schema objects for deployment
To automatically update the test environment with the development version of that table, all you need to do is select the table in the top window to put it in the set of objects that will be included in the deployment (Figure 4).
Figure 4: Selecting objects to deploy in Schema Compare for Oracle
Once you’ve selected the set of objects to deploy you simply run the Deployment Wizard. This allows you to automate the creation of your deployment script — which as highlighted earlier can be very time consuming to do manually — and, if you choose, manage the actual task of migrating the schema changes across.
The first step in the wizard give you the option of deploying the changes through Schema Compare for Oracle or creating a deployment script which you can export and run through your IDE (Figure 5 and Figure 6).
Figure 5: Deploy using Schema Compare for Oracle
Figure 6: Create a deployment script to run through your IDE
For this example imagine you have chosen to use Schema Compare to automate updating the changes to your schemas. Before the tool generates the update script, you will be prompted to review any dependencies (Figure 7).
Figure 7: Schema Compare for Oracle identifies any dependencies for the objects selected for deployment
The migration script is then generated extremely quickly. Figure 8 shows the script for the objects selected above. It's a relatively simple example but you can see a good 50 lines or so of fairly intricate SQL has been created. You can see the very first line shows adding the column called ‘DATEVALIDFROM’ to the test schema, which was identified above. As you read down the script you can see the other objects that were included in the deployment.
Figure 8: Deployment script to update schema changes
There’s lots of nice functionality included in the Deployment Wizard: you can see a summary of what the script does if you don’t want to read all the SQL (Figure 9); and if anything odd is going to happen Schema Compare for Oracle will warn you (Figure 10).
Figure 9: Summary of the changes deploying the script will make to the schema
There could be situations where the data could get truncated, e.g. if you’re reducing the size of a column, and the tool will warn you that the data will be truncated.
Figure 10: Warnings that apply to the deployment
If you want to use Schema Compare for Oracle to manage the deployment for you automatically, you simply select the “Deploy Now” button. The software will bring up a final warning that the changes you have asked it to make will go ahead. You also have the option to open the deployment script in your chosen IDE e.g. Oracle SQL Developer or PL/SQL Developer, to review or execute.
When a deployment has completed you can go back to the main screen and see that both instances of the database are in sync based on the changes that were made (Figure 13).
Figure 11: Completed Oracle database deployment, comparison of schemas
This example shows how you can eliminate writing schema update and comparison scripts by hand, speed up your deployment process, and gain greater visibility of your schemas’ structure across your environments by using Schema Compare for Oracle.
Try Schema Compare for Oracle for free
Download the free 28 day trial of Schema Compare for Oracle and you can be looking at a detailed side-by-side view of your schemas in minutes. All functionality is included, we offer full technical support, and there's no obligation to purchase when the trial expires.