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

From: Thomas Kellerer <>
Date: Mon, 10 Nov 2008 10:02:08 +0100
Message-ID: <>

Noons, 10.11.2008 09:45:

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

> It's called database consistency.
> Only real databases have it.

But creating objects in a database needs to be consistent as well. I don't see why having transactional DDL contradicts database consistency.

Actually I think it adds to consistency.

>> DDL statements
>> simply write information (insert, delete and update) to data
>> dictionary, so why DDL statement cannot be rolled back.

> Oh no they most certainly don't just do that!
So what else are they doing?

 >> I was learning SQL Server few months ago and I noticed that SQL Server

>> does allow rollback of DDL statements if I am not mistaken.

> It does not.

Yes it does (Note that I do not like SQL Server at all, but that is definitely a plus - or it would be if SQL Server wasn't that awful when it comes to locking...)
>> Rollback of all staements should be allowed not just
>> of DML statements.

> Actually, it should not. There is no requirement
> anywhere for such in any of the available standards
> for relational database architecture or design.

There might not be a requirement, but it *is* very handy.

Just think of an database upgrade script that fails in the middle. With transactional DDL I simply do a rollback fix the cause of the error and start the script again. With non-transactional DDL I need to remove all objects that were created until then to put the DB into the state before I ran the script.

Thomas Received on Mon Nov 10 2008 - 03:02:08 CST

Original text of this message