Comparing Schemas: Red Gate Schema Compare for Oracle

This week I attended a webinar by Cary Millsap, organized by Red Gate. It was called "Real Developers DO use Tools" and was focussed on, well, tools. At the end of Cary's talk, James Murtagh showed a demo of their Schema Compare tool. The demo wasn't flawless, but he recovered nicely :) (note to self: don't do live demo's). And this - the demo, not the occuring error - triggered a memory that I wanted to write a blog on the Schema Compare tool.
If you missed the webinar you can find it on the website of Red Gate here: http://www.red-gate.com/products/oracle-development/deployment-suite-for-oracle/webinars/webinar-archive
I had the pleasure of meeting James Murtagh during the UKOUG annual conference at the end of last year. James also hosts the All Things Oracle site. While we were discussing different topics (even non-oracle) he asked me which tool is my "development tool of choice". The tool I use predominantly is PL/SQL Developer by Allround Automations. "We have a plugin for that",... time to take a closer look at the tool.
First the installation of Schema Compare, simply following the wizard and you're done in a jiffy.

Even though I found out later, if you're on at least PL/SQL Developer 9.0.1, that it is not necessary to download the product separately. This can also be done from within the plugin for PL/SQL Developer. When you're on an older version of PL/SQL Developer, as I am, you need to install the plugin yourself.
Next the plugin, which can be found on the website of Allround Automations in the plugin listing.

Installing a plugin in PL/SQL Developer is always very easy, just double click the executable and you're set.
The big question with plugins in PL/SQL Developer is: "How can you access the plugin?" In this case it is very simple, not hidden in some of the other menu's, but available in the main toolbar.

Time to try out the Schema Compare from my PL/SQL Developer environment. It start the tool automatically, and prompt you for the database connections. Unfortunately it doesn't use the same "connection list" as PL/SQL Developer, so you will have to enter the connection information yourself. At least for me it didn't work as I hoped, I checked with James Murtagh and showed me that it actually does work. Maybe a PL/SQL Developer version specific thingy (I use version 8).
Fortunate for me, the plugin now support Oracle Instant Client (thanks Tom).

Just fill in the Source and the Target database connection information, pick the schemas that you want to compare and push the "Compare Schema" button.
Next is a progress window shown... The following screenshot took me several takes to get, the comparison is done really fast. Of course this was done on my test database with only several hundreds of objects in the user schemas, but even on our production system the speed was amazing.... The following screenshot took me several takes to get, maybe I'm just too slow.

When the comparison is done, you get an overview of all the differences that exist between the two schemas as shown in the next screenshot.

Double clicking on the big blue arrow will reverse the comparison, which can come in very handy if you reverse the Source and Target databases, right, James?
Now you can inspect all differences, and decide which differences you want to deploy to the Target schema. Just check the boxes of the objects you want to deploy.
After you make your selection, start up the Deployment Wizard (top of the screen, which is visible in the screenshot. Resizing the window apparently moves the button to the second line of buttons.).
In three steps you can deploy what you have selected.
In step one, you decide the deployment method - I choose a deployment script. What is kind of odd that the default "Open the script in " option is set to Oracle SQL Developer, even though I started the program from PL/SQL Developer. Can be changed easily, odd none the less.

The second step analyses the dependencies for you. In my example I choose to deploy a few sequences, which apparently have a dependency in some triggers, packages and even a view.

The last step is an overview of the steps that are going to be taken, like dropping object, creating or recreating objects. Always a good idea to review, you can never be too careful.

Maybe it's a language thing - I'm not a native English speaker - but in step two of the deployment wizard, where it shows the referenced objects, there is a check box with the label "Deploy all affected objects (Recommended)". My assumption was that the objects listed, like the packages, triggers and the view were going to be deployed to the target database. As often with assumptions, I was wrong. The only thing regarding these objects in the deployment script is "Drop the object", which seems odd to me. Deploying sequences can - at best - invalidate some objects, no reason to drop the affected objects.

Overall Impression

I like the way it works, right from my development tool I can start it up. Would be nice if I could use my existing database connections (all listed in PL/SQL Developer) but that's only a minor detail. But this should work and when I upgrade to a newer version of PL/SQL Developer it should be gone. The connections can be saved in Schema Compare as well.
The differences between the two schemas are mapped out in a very orderly fashion. Differences of objects which exist in both the source as the target schema, objects that exist in either the source or the target, and there is even a section of objects which are identical in both schemas. This list is very clear, easy to navigate through and the differences per object are highlighted so you can easily see the differences.
Personally I would also opt to have a script which I can review and keep in version control, instead of having it deploy automatically. Maybe I'm just paranoid, but that's the way I would do it. Of course you can also do an automatic deployment, followed by another comparison, and deploy again, continuing until there are no more differences left, but this wouldn't be my choice.
This doesn't mean you can't review the script before you run it, you can. And I strongly suggest you do this.
What really impressed me was the speed of the comparison. Comparing two schemas is done really quickly, even with a couple of medium sized production schemas.

Links

• All Things Oracle
• RedGate Oracle Deployment Tools
• PL/SQL Developer