Picture of Schema Compare for Oracle

Schema Compare for Oracle

von Red Gate

Der schnelle Weg um Oracle Schemas zu vergleichen und anzuwenden

Schema Compare for Oracle

Der schnelle Weg um Oracle Schemas zu vergleichen und anzuwenden:

  • Teil von der Deployment Suite for Oracle
  • Identifiziert Differenzen zwischen Ihren größten Umgebungen innerhalb von Minuten - kleinere Schemas benötigen nur Sekunden 
  • Eliminiert Fehler
  • Vergleicht vielfältige Schemas
  • Vergleicht Schemaobjekte mit Erstellungsskripten 
  • Wendet Datenbanken vom Source-Control an
  • Automatisiert Entwicklungsschritte 

View schemas side-by-side

Drill down into the DDL for each object in your schema to see highlighted differences. Filter by object, or type an object name to find it fast. Compare databases, snapshots, and scripts folders. Easily find and fix errors in deployment.

Deploy schema updates

Automatically create error-free synchronization scripts to deploy only the schema changes you want. Get deployment notifications about dependencies, truncations, and other issues. Deploy using Schema Compare or your IDE, or save a SQL script to share with your team.

Work with the command line

Carry out any task using the command line tool. Automate scheduled tasks like comparisons and deployments for continuous integration.

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

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.

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

Figure 2: Compare two Oracle schemas and see differences highlighted side-by-side in Schema Compare for Oracle

Figure 3: Highlighted differences in the DDL for a selected schema object

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.

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

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.

Figure 4: Selecting objects to deploy in Schema Compare for Oracle

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

The migration script is then generated extremely quickly.

Figure 8 shows the script for the objects selected above.

Figure 7: Schema Compare for Oracle identifies any dependencies for the objects selected for deployment

Figure 8: Deployment script to update schema changes

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.

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

Figure 10: Warnings that apply to the deployment

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.

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

Summary

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.

SKU Name Upg Mon. products.netprice products.grossprice
155107 Schema Compare for Oracle, 1 User, 1 Jahr Maintenance Verlängerung eng. Win. User Pack 12 €126.89 €151.00
153487 Schema Compare for Oracle, 1 User, Lizenz inkl. 1 Jahr Maintenance eng. Win. User Pack 12 €640.34 €762.00
116897 Schema Compare for Oracle, 10 User, 1Y, EN, WIN, LIZ+MNT User Pack, Gesamtpreis 12 €5097.48 €6066.00
116898 Schema Compare for Oracle, 10 User, 1Y, EN, WIN, MNT User Pack, Gesamtpreis 12 €1016.81 €1210.00
116899 Schema Compare for Oracle, 10 User, 2Y, EN, WIN, LIZ+MNT User Pack, Gesamtpreis 24 €6115.13 €7277.00
116900 Schema Compare for Oracle, 10 User, 2Y, EN, WIN, MNT User Pack, Gesamtpreis 24 €2034.45 €2421.00
116901 Schema Compare for Oracle, 10 User, 3Y, EN, WIN, LIZ+MNT User Pack, Gesamtpreis 36 €7131.09 €8486.00
116902 Schema Compare for Oracle, 10 User, 3Y, EN, WIN, MNT User Pack, Gesamtpreis 36 €3051.26 €3631.00
116903 Schema Compare for Oracle, 5 User, 1Y, EN, WIN, LIZ+MNT User Pack, Gesamtpreis 12 €2704.20 €3218.00
116904 Schema Compare for Oracle, 5 User, 1Y, EN, WIN, MNT User Pack, Gesamtpreis 12 €539.50 €642.00
116905 Schema Compare for Oracle, 5 User, 2Y, EN, WIN, LIZ+MNT User Pack, Gesamtpreis 24 €3242.86 €3859.00
116906 Schema Compare for Oracle, 5 User, 2Y, EN, WIN, MNT User Pack, Gesamtpreis 24 €1077.31 €1282.00
116907 Schema Compare for Oracle, 5 User, 3Y, EN, WIN, LIZ+MNT User Pack, Gesamtpreis 36 €3781.51 €4500.00
116908 Schema Compare for Oracle, 5 User, 3Y, EN, WIN, MNT User Pack, Gesamtpreis 36 €1615.13 €1922.00