Re: Why Oracle does not allow rollback of DDL statements?

From: Laurenz Albe <>
Date: 12 Nov 2008 09:04:46 GMT
Message-ID: <>

joel garry <> wrote:

>> I have always been surprised why Oracle does a implicit commit when
>> DDL statements (e.g create table ?) are executed.

> I don't know the real reason you can't rollback DDL, but I suspect
> part of it is simply the evolution of the code. By now, it would be
> difficult. And there are workarounds in the modern versions.
> As far as Serge's assertion that customers want to be able to change
> dicks in the middle of a screw, I mean, change defs in the middle of a
> view, that's as silly as some other things customers "want," like
> dirty reads. There may even be a legitimate requirement, but that's
> no reason to make it easy at the expense of other, more reasonable and
> ordinary things. I wonder how such things don't violate Codd's
> Physical Data Independence rule - they logically impair applications.
> Just because there is a Codd rule that states the same language must
> be used to access the online catalog doesn't mean all possilbe
> language features need to apply to the catalog.

I think that it is legitimate that Oracle handles things that way, particularly if you consider that it is software that has been around for a long time and carries a lot of legacy cruft.

It would probably be really difficult to change things now.

But I don't think that, just because of that, it is necessary or helpful to conjure up proofs and arguments why transactional DDL is a Bad Thing.

In the ugly case that someone tries to change data definitions while concurrent users are using the data, a transactional DDL system will not behave so much different from Oracle anyway, because any sane approach will prevent concurrent data modification.

Where I find that transactional DDL (as for example implemented by PostgreSQL) is very useful and a Good Thing is for DDL scripts.

That way you can pack all DDL for a script that updates your application (during a maintenance window of course) in a single transaction, and it will be applied all-or-nothing.

Otherwise, if the script fails halfway through, you will either have to figure out where it went wrong and continue from that point, or (as Oracle does it) you have to write all your DDL scripts so that they are idempotent, i.e. it does not matter if you apply them twice.

Laurenz Albe Received on Wed Nov 12 2008 - 03:04:46 CST

Original text of this message