Re: Why Oracle does not allow rollback of DDL statements?
From: Thomas Kellerer <YQDHXVLMUBXG_at_spammotel.com>
Date: Mon, 10 Nov 2008 10:02:08 +0100
Message-ID: <6nqbkgF7okkU1@mid.individual.net>
>
> It's called database consistency.
> Only real databases have it.
>
> Oh no they most certainly don't just do that!
So what else are they doing?
>
> 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...)
>
> Actually, it should not. There is no requirement
> anywhere for such in any of the available standards
> for relational database architecture or design.
Date: Mon, 10 Nov 2008 10:02:08 +0100
Message-ID: <6nqbkgF7okkU1@mid.individual.net>
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