The issue

Schema A contains version v1 of the DDL of an in-house application.  The developers of that application deliver  version v2 and you are tasked to implement on schema A the DDL differences between v1 and v2.

 

What SQLDeveloper can do

 

SQLDeveloper offers a tool, called Database Diff,  to compare several schemas, or objects from several schemas.

P1.jpg

To keep it simple, let’s compare 2 schemas without taking constraints into account:

P2.jpg

Next screen, select the objects you want to be compared:

P3.jpg

In the next screen, you can filter out some objects. Here we’re going to skip it because we want to compare all the objects in both schemas.

 

Next, there’s a summary of what we’ve chosen. Always review it for mistakes!

P4.jpg

When you click on Finish, the following dialog box appears:

P5.jpg

This may run for a long time, and all the while your SQLDeveloper is not available.  But you can click on “Run in Backgound” and use your SQLDeveloper again (you can then monitor the progress of Diff Database in View/Task Progress).

 

When that process finishes, the following “Diff Report” tab appears:

P6.jpg

The objects with a green plus sign are the new objects: those that are in one schema but not in the other.  The objects with an orange warning sign are the objects that are in both schemas but do not have the same definition.

 

If you click on one of those objects, a block appears below with the details of the difference:

P7.jpg

Here the “REPEAT_COUNT” column exists in one schema but not in the other.

 

In the “Script” tab, you will find the necessary DDL to implement the difference:

P8.JPG

And if you click on the “SQL” icon at the top left-hand corner:

p9.jpg

you are given the possibility to create one or several DDL scripts to implement those differences:

P10.jpg

Single File: all the necessary DDL code will be saved to one file of your choice

Separate Files: will create one file per object

Type Files: will create one file for all the indexes, another files for all the tables, another file for all the views etc.

Separate Directories: will create one file per object and sort those files per object type in sub-directories, and create a global script that runs all the others:

--  File created - Wednesday-May-25-2016  

--------------------------------------------------------

@D:\hello\SEQUENCES\SEQ_PROCESS_ID_SEQ.sql

@D:\hello\SEQUENCES\SEQ_ACTIVITY_ID_SEQ.sql

@D:\hello\TABLES\DS001_QZ_SIMPLE_TRIGGERS.sql

@D:\hello\TABLES\DS001_PROCESS_INSTANCE_IN_USE.sql

@D:\hello\TABLES\DS001_ACTIVITY.sql

@D:\hello\INDEXES\IDX_QZ_FT_JG.sql

Clipboard: will copy all the necessary DDL to the clipboard

Worksheet: will copy all the necessary DDL to a new SQLDeveloper worksheet, like this:

P11.jpg

so you can run the DDL straight away from that new worksheet.

 

That was an overview of what Database Diff can do for you.  Please leave comments or questions below.