A common problem that Oracle developers and DBAs encounter when looking at deploying Oracle schema and data changes is interacting with many different Oracle environments. A team or several separate developers could all be working towards getting some new features into the database. Usually a test environment will sit between development and production, and further down the line is likely to be user acceptance testing, pre-production staging and planning, and production or multiple production servers.

The focus of this article is to think about how Oracle developers and DBAs manage changes between different database environments. How are schema changes deployed from one environment to another? What options are available to compare two Oracle schemas before and after migration? The focus will mostly be on schema, but the article will also touch on how you might be handling data propagation as well.

A typical scenario

You’ve got a number of changes to schemas in your development server and as part of a release you’ve, for example, written a new stored procedure, some new triggers and you’ve been changing the way the DDL is written for some tables and some new definitions. These changes could be relatively straightforward or they could be very intricate. So the real challenge is when you get to the point where you’re completely happy with the state of your development instance, you need to test the work before it can deployed through to production. The challenges are: how do you go about moving those changes from one environment to another? And how do you compare schemas afterwards to verify the changes?

Different approaches to deploying Oracle schema changes

1. Write the scripts by hand

It’s still fairly common for developers or DBAs to write SQL scripts by hand to update changes from one instance of their database to another. This approach has a good plus point: it gives the developer complete control over the script, so they know that whoever runs that script is running a script that they wrote. However, this approach has a lot of downsides. It’s very time consuming, and developers will need to factor in the extra of time needed to write update scripts in their database development cycle.

Writing the update scripts by hand isn’t particularly fun and because of this it’s easy to make a mistake. It’s a job that might be done at the end of the day or in a rush, and so developers must make sure that their scripts are accurate. One of the most important points is that if you write all the scripts manually, the validation step of checking that the script synchronises the two schemas is missing. This validation will also need to be done by hand, by running a query to compare the two schemas and check that they look the same.

2. Hand over responsibility to the DBA

Some developers will hand over all responsibility for updating and checking schema changes to the DBA. This is common in organisations where the DBAs want to limit the number of people involved with changing the database. However, it can be hard for developers to keep up-to-date with getting their development changes through into the staging or testing environment. The DBA will also have lots of other things to manage, like maintaining the servers, backing them up, and ensuring they’re performing correctly.

3. Build the database from scratch

Another, and relatively valid, approach is to completely wipe the test database and build it from scratch. You simply backup the development database, copy the schema and data files across to the test server, and restore them. You now have a perfectly good test instance which you know is the same as the dev instance because it’s restored from the backup. If these databases are large it would be time-consuming to back up and restore, and you will inevitably be under time pressures to push the changes across to test. But probably the most important point is that it’s a very big overkill if you’ve only got a small number of changes. You’ve also got the issue of data; typically test databases contain a whole bunch of the test data which won’t be in the development environment.

4. Use tooling to assist you

The three options summarized above will all manage the process of moving schema changes from one environment to another, but they all have their downsides. The common theme amongst these downsides is time, and tooling is an option that can address this.

In the next section we’re going to look at how the tools in the Deployment Suite for Oracle meet both the challenges of deploying schema changes from one environment to another and ensuring both instances are in sync after migration.

Schema Compare for Oracle

Red Gate’s Oracle Schema Compare is by far the best schema comparison tool on the market; It now takes just 45 seconds for me 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

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.

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).

Connect Schema Compare for Oracle to your Oracle databases
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.

See differences in your schema objects highlighted side-by-side in Schema Compare for Oracle
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.

See highlighted differences in the DDL for schema objects across two instances of your database
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).

Selecting objects to deploy in Schema Compare for Oracle
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).

Deploy using Schema Compare for Oracle
Figure 5: Deploy using Schema Compare for Oracle

Create a deloyment script to run through your IDE
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).

Schema Compare for Oracle identifies any dependencies for the objects selected for deployment
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.

Deployment script to update schema changes
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).

Summary of the changes deploying the script will make to the schema
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.

Warnings that apply to the deployment
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).

Completed Oracle database deployment, comparison of schemas
Figure 13: 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.

Data Compare for Oracle

We plan to integrate Data Compare for Oracle in our nightly builds. At the moment, all our data related changes have to be scripted manually, which is error prone and not very productive. With Data Compare for Oracle, we can easily compare the data in two different database builds and generate the synchronization script. It’s going save at least an hour of development time each week – and stop me going grey so quickly!

David Hart, Senior Application Developer, T2 Systems

The Deployment Suite for Oracle also includes a tool to help you manage deploying the data associated with schemas to actually make the database function. This configuration or lookup data alongside schema changes has a tendency to change during development and it’s quite often necessary to write scripts that push changes in the lookup data up into different environments. Data Compare for Oracle works in a similar way to Schema Compare for Oracle to allow you to compare tables, see which records are different, and then generate the scripts to move those records between one environment and another.

See differences in your schema table data highlighted side-by-side in Data Compare for Oracle
Figure 14: See differences in your schema table data highlighted side-by-side in Data Compare for Oracle


Some different approaches to comparing Oracle schemas and migrating changes have been highlighted. Deploying changes across your database is not a simple procedure; there are lots of things to be aware of, both in terms of the advantages and disadvantages of using the different approaches. Time and accuracy are important aspects of any deployment, and the Deployment Suite for Oracle offers a solution which will speed up and improve the accuracy of your main deployment tasks.

Free 14 day trial of the Deployment Suite for Oracle

To see how the Deployment Suite for Oracle will speed up your database comparison and deployment tasks, download a free 14 day trial:

You can also read the worked example on the Red Gate website and view more screenshots and videos.