July 9 2018

Overview

There are a class of tools whose job it is to managed database schema changes and provide the ability through the tool's own change logs to implement the changes to higher environments.  Liquibase by Datica and Flyway by Boxfuse are two such commercial tools.  The tools provide a very controlled means of migrating schema changes from DEV to TEST and to PROD, the goal to preserve the underlying data.  The tools provide a database agnostic means to do this and to perform schema rollbacks if necessary.

Undoubtedly for large database schema installations commercial schema migration tools like Liquibase or Flyway makes sense.  However, there are times when you simply want to list the differences between two schemas quickly, producing a "Diff Report" and optionally to create the SQL to show the alters necessary to upgrade the destination schema.  SQL Developer/JDeveloper will do this quickly and effectively.  This topic is not a comparison of SQL Developer/JDeveloper to schema migration tools like Liquibase and Flyway but instead, a quick tutorial on leveraging this widely adopted Oracle tool for the list of schema differences.

Diff Report Steps

In the use case described here there are two Oracle database schemas compared, one in DEV and the other in TEST.  All schema artifacts will be compared (tables, indexes, procedures, packages, sequences and, functions).

  1. Open SQL Developer.  Here SQL Developer Version 18.1.0 which is a the current version as of this writing (July 2018).  (info) This feature was also tested in JDeveloper 12.2.1.2. SQL Developer About
  2. Create database connections to the DEV and TEST databases to be compared (highlighted in blue above).
  3. Select Tools...Database Diff option.
     Select Source and Destination
  4. In the first step of the Diff Wizard select the Source and Destination connections.  The DDL generation defaults are comprehensive.  For schema differences alone, ignore storage and tablespace differences. 
  5. In the second step of the Diff Wizard select the schema types to be compared.

    Select Object Types


    (info) If you're tracking down a specific change un-check all but the object types you have not interest in seeing.  It's less churn to create the Diff Report as well.

  6. In the third step of the Diff Wizard perform a Lookup and then shuttle the objects to be selected list. 

    Select Object Instances


    (info) You may use "wildcards" in your Lookup request which may be prudent for large schemas.

  7. In the Summary review your previous selection and select Finish to create the Diff Report.  The comparison will take a minute or two, be patient.
  8. The Diff Report will display in a graphical view the differences it found.  Optionally you can select the Show Equal Objects to see what has is unchanged in the two schemas.

    Review Diff Report

  9. In the upper left corner of the Diff Report is a SQL icon, select this to generate the SQL for the differences shown.
  10. The SQL generated will contain the necessary ALTER and create/replace statements for the differences shown.

    Generated DDL

Best Practices

  • If you elect to run the DDL do so on a local environment first before undertaking this in the real environment (DEV or PROD).
  • There will be a few issues when running the DDL, for example the CREATE TABLE will contain a primary key constraint and then further down will be the DDL to create the index for it which will fail.  Remove the latter from the DDL.
  • The DDL should be versioned in source control. Consider appending the DDL file name with a date or version, as schema changes will evolve over time so this may be necessary.
  • Schema rollbacks may be necessary so maintains the original schema with properly dated or version-identified DDL files is one mechanism.  This is where Liquibase and Flyway earn their reputations so consider these tools for larger or newer projects.

Summary

The SQL Developer Database Diff feature can quickly identify schema differences and is oriented to Oracle databases.  Other tools should be considered to manage new or complex schema migrations.

About the Author

Greg Hughlett

Greg has more than twenty-five years of experience in all phases of design, development, and implementation of software applications.  He has developed and architected BPM/SOA technologies for more than ten of those years from Fuego BPM to BEA AquaLogic BPM to Oracle BPM/SOA 11g.  He has worked for clients within banking, financial services, Life Insurance,  Health Care, public sector and telecommunications industries.  His areas of expertise include Oracle SOA and Oracle BPM (formerly AquaLo

Join the Conversation

Enter your first name. It will only be used to display with your comment.
Enter your email. This will be used to validate you as a real user but will NOT be displayed with the comment.