Agile and database design

I sat in a room with about 50 other database administrators and developers a little while ago. The consensus was that Agile methods were being interpreted as a requirement to drop good principles of database design.

But does “fail fast” work for the database layer of an application?

A classic case of design revision is a concatenated name column. Let’s say version A of the application has the definition name VARCHAR2(100), version B (first_name VARCHAR2(100), last_name VARCHAR2(100)).

If you’re the developer working on version B, thinking “wouldn’t it be nice if they’d put a bit more thought into this table in the first place”?

But we are where we are**, so is there a way of going from Version A to B without an application outage?

Well, yes there is.

Let’s take a look at FLIGHTLOGS.PILOTS as defined here:

The NAME column was originally a single column, but the application has changed and now demands names separated into FIRST_NAME and LAST_NAME.

Our current version is ORA$BASE, the original default version for any objects in an edition-enabled schema.

To make things a bit more interesting, I’ve also defined some PL/SQL objects. GET_NAME will need to change from Edition ORA$BASE to E2, in order to continue to work.

GET_PILOT_AIRCRAFT will not change, but we will use it to see what happens to PL/SQL objects when you want to clear down an old edition.


So now we add FIRST_NAME and LAST_NAME to the base table FLIGHTLOGS.”pilots”.

For now GET_NAME continues as before. It’s looking at the editioning view in Edition ORA$BASE, FLIGHTLOGS.”PILOTS”. (Thanks to Chris Saxon for the lower case/upper case suggestion.)

When we set the Edition to E2, we can compile the new version of GET_NAME, concatenating FIRST_NAME and LAST_NAME from the new columns, based on the Edition E2 version of FLIGHTLOGS.”PILOTS”.

Let’s have a look at our objects.

GET_PILOT_AIRCRAFT still exists in Edition ORA$BASE. So we can’t drop ORA$BASE. If we compile it in Edition E2, it is “actualised” in the new edition.

Now we can use the new table definition in the new version of get_name.

Note the compilation can, of course, go right ahead with production users still using the earlier edition. In a real case, you can recompile all editioned objects in a new edition without any production downtime, library cache locks or users seeing “ORA-04068: existing state of package X has been discarded”.

With all objects in edition ORA$BASE moved to the new edition, we can now drop the old edition. (I’ve been advised by an Oracle staffer that this is not essential, old editions don’t use up a great deal of space or impact performance significantly. You can have thousands of editions without a problem, but see the first gotcha below.)

So Agility is possible with database objects. A little care is necessary to ensure users see only the versions they should see, until new versions go live.

If User Acceptance Testing in production is necessary, that’s possible too. Cross Edition triggers enable it. In my example that’s simple, but in a real production application, it might be a reason to be less “agile” with database design, and try a “get it right” approach.

And finally… cleaning up old editions means we can continue to move forward, confident there’s no long chain of old objects to clean up.


Editions are regarded as a dependency chain. You cannot drop a child edition where the parent has objects not actualised in the child. This makes perfect sense, you wouldn’t generally want to drop an edition where objects did not exist in the new version.

Here in edition A2, I compiled a copy of GET_PILOT_AIRCRAFT.

So tidying up becomes important. Rolling forward your PL/SQL objects into new editions allows you to keep the chain short. One set of Oracle advice is to have no more than two or three editions at a time. Yesterday’s, today’s and tomorrow’s editions might be typical. Developers can release into “tomorrow” at any time.

(To avoid confusion, one convention is to name editions <prefix>YYMMDD.)

Developers can prefix scripts with an ALTER SESSION to set the edition to tomorrow’s edition as required.


There were also some gotchas with registered XML schemas – but these can be worked around by leaving such objects in their own, non-editioned schemas. Abstract data-types also require close attention.

** “We are where we are” is my most disliked phrase in Information Technology.

%d bloggers like this:
search previous next tag category expand menu location phone mail time cart zoom edit close