Follow-up on comparing two Oracle schemas

Over 4 years ago I wrote a blog post about software that helps you to compare two Oracle schemas. That post is accessed a lot and I still get questions about it, so I decided to write a follow up on that post as things change over time.

Comparing two Oracle schemas is still something I have to do regularly, e.g. if I want to compare a development, test and production instance after a deployment of an application.

Alongside Oracle Application Express (APEX), I use Oracle SQL Developer daily. They have a built-in Database Diff tool which works, but I tend to use other tools as well, as it’s hard with SQL Developer to see exactly what has changed. We built our own tool in APEX that compares schemas based on dbms_metadata, which we use when we have access only through the APEX interface. But when I can run things from my own system, I like to use Schema Compare for Oracle.

The people of Red Gate wrote a nice post on how to use their software, with screenshots and steps you can follow, so I won't repeat that. Instead I’m going to compare it against the Diff Tool built into SQL Developer.

Simplicity

The older I get, the more I can appreciate user-friendly software. That is something I try to do in the software I write myself, follow the KISS principle = keep it simple stupid.

For me software has to look good, ’be easy to use and do what it’s intended to do in an efficient way. If I want to compare two schemas I just want to follow a couple of steps:
1. Select my source and target database
2. Select the schema(s) I want to compare
3. Optional - select what exactly it should compare (tables, packages etc.)
4. Get an overview of the results
5. Produce synchronization scripts

Schema Compare for Oracle (SCfO)

Here’s how it works in Schema Compare for Oracle.

When you open Schema Compare you have to create or open a project.


It asks you for the source and target database you want to connect to and which schema you want to compare. An advantage in SCfO is that you can compare multiple schemas at once.
Another thing I found interesting was the option to compare against a Snapshot. You basically take a “picture” of your schema at a certain moment in time and compare against that. Very handy when you can’t access both schemas from the same location. Or if you develop applications and want to generate upgrade scripts, I see the use of snapshots too.

But let’s get further with the normal schema compare.

In SCfO you don’t select the objects first, you just hit the Compare Now button and SCfO starts to compare the whole schema directly. At first I thought, I don’t really want you to do that as you are doing a lot of work for nothing, but the more I used the tool, the more I appreciated it. To start a comparison is very easy and quick...

One thing I found handy as well is the Options tab, which allows you to define if the tool needs to ignore white spaces, storage clauses etc.


When we are happy with the options, we click the Compare Now button and we get a screen with the progress.


Once it’s finished you have a complete overview of the differences in both schemas.


I like this screen a lot as it has many more functionalities than you would first imagine. The filter (find box) is very handy to find some specific objects quickly. You can also sort by different things and just from the way it looks it is so easy to understand what is different in which schema and database! But the nicest thing is when you click on a row where there are differences. It shows you both versions and highlights the differences. No need for an extra tool or text editor to get that information out. It’s just there.


If you want to create an upgrade script or make both schemas equal you just have to use the Synchronization Wizard...

The wizard asks you what it has to do and generates a script for you or automatically synchronizes both schemas based on the objects you selected.


I think Schema Compare for Oracle does it really well. So let’s compare it to the comparison tools that come with Oracle SQL Developer.

Oracle SQL Developer

In SQL Developer you go to Tools - Database Diff.

The first time it will tell you it’s using Oracle Change Management, a payable option of the Oracle database, and you have to acknowledge you have a proper license to use that.


Next, it will pop up with a screen that allows you to select a source and destination connection and immediately asks you what objects you want to compare.


Clicking on Next shows you a screen where you can select the individual objects.
You can view all objects at once, or change the select-list to only see tables, sequences and select the objects that way. This might be a good thing to do if you have many objects!


Once you click Finish it will compare the objects and present you the result in a Diff Report.


You can click on the green SQL button and it will generate the script for you based on the differences you selected.

So it’s rather straightforward to compare two Oracle schemas in SQL Developer, but there are some things I would prefer a bit different.

This is my wish list:
1. Not be forced to have the Change Management license for your Oracle database. The price depends your Oracle Database license, but it can be high if you just want to compare two schemas through SQL Developer.
2. Be able to compare multiple schemas at the same time.
3. Get a cleaner Diff Report, which allows me to add filters to it and allows me to see what exactly is different between the two versions. At the moment I just see a count there, when I click on the row. I get the script to generate that version, but I couldn’t find a way to see exactly the source and target and compare them side-by-side.

Point 3 is my biggest issue and that is why I searched for something else that made my life easier and finally came across Schema Compare for Oracle. Although Red Gate created a Windows only version of Schema Compare for Oracle, so as a Mac user I need to run it through a VM, I find it worthwhile to do it. The interface of SCfO is very clean, it’s easy to use and it gives me most features I’m searching for. There is even a SQL Developer plugin for SCfO (search for Schema Compare for Oracle in the SQL Developer Plugin repository).

So is Schema Compare perfect and is it better in every aspect than the Diff Tool in SQL Developer? Almost, but not completely. SQL Developer supports the comparison of Materialized Views (and their logs) and Database links, which SCfO doesn’t do in the release I tested (1.3).The version of SQL Developer I used was 2.1. I’m not sure the Diff Tool in SQL Developer 3 (which will be released in a few weeks/months) will be different...

Happy comparison!

APEX 4.0.1 Patch released

Hi just read Joel Kallman's post that the Oracle Application Express (APEX) team released 4.0.1 which corrects a number of issues found in APEX 4.0.

I recommend going to 4.0.1 if you have 4.0 installed as soon as possible.

If you still have 3.x running and want to upgrade to 4.0 you can directly download the 4.0.1 release and install that.

The new version of APEX is 4.0.1.00.03.

Add Default to Tabular Form in APEX

If you want to set a default value for a column in your tabular form, you can set the Default Type to "PL/SQL Expression or Function" and put into Default e.g. 'N'.


That sets the default for that column to the static text 'N'.

Upgrade to APEX 4.0 - Known Issues - Workarounds and Patches

If you upgrade your application from APEX 3.x to 4.0 you might encounter some issues.

At ODTUG I did a live migration of an application written in APEX 3.2 to 4.0 and I talked about some of the issues I discovered and how I worked around them. I'm doing the same presentation at APEXPosed in Brussels in October. You also might have seen my previous posts where I upgrade DG Tournament from APEX 3 to 4.

On the Known Issues page you see the issues categorized nicely. If the issues can be fixed by working around it, it's also mentioned there. Oracle is working hard on a patchset (4.0.1) and they hope to release it soon. On Metalink you can already find patches for specific issues too.

I plan to write some other blog posts with steps I did when upgrading DG Tournament to APEX 4.0.

APEX Father is speaking ...

If you didn't pickup another important APEX blog to follow yet... you definitely want to follow Mike Hichwa.

Mike is the "father" of APEX, it must be over 10 years ago he started with the development of APEX (HTMLDB/Project Flows).

Mike is a VP at Oracle and is now responsible for a lot more than APEX alone. He also has SQL Developer and many other tools all related to Database Development.

ODTUG comes to Europe in October! Registration open!

As announced on the latest OBUG APEX SIG, we will participate in the first ever ODTUG event in Europe!

It's a two day conference focused on PL/SQL and APEX, with two full tracks for each.

If you look at the Agenda you will see the presenters are well known in the APEX community and amongst the best in their field. On the PL/SQL side you have ofcourse Steven Feuerstein and a lot of other big names.

Registration is open now, so don't wait to register, places are limited!