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.
To keep it simple, let’s compare 2 schemas without taking constraints into account:
Next screen, select the objects you want to be compared:
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!
When you click on Finish, the following dialog box appears:
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:
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:
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:
And if you click on the “SQL” icon at the top left-hand corner:
you are given the possibility to create one or several DDL scripts to implement those differences:
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
Clipboard: will copy all the necessary DDL to the clipboard
Worksheet: will copy all the necessary DDL to a new SQLDeveloper worksheet, like this:
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.