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

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 11 Nov 2008 16:08:06 -0800
Message-ID: <1226448472.452418@bubbleator.drizzle.com>


joel garry wrote:
> On Nov 9, 3:36 pm, zigzag..._at_yahoo.com wrote:

>> I have always been surprised why Oracle does a implicit commit when
>> DDL statements (e.g create table ) are executed.  DDL statements
>> simply write information (insert, delete and update) to data
>> dictionary, so why DDL statement cannot be rolled back. Am I missing
>> some thing?
>>
>> 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 lloks
>> like there is something in Oracle architecture which prevents rollback
>> of DDL satements. Rollback of all staements should be allowed not just
>> of DML statements.
>>
>> Appercaite your insight.

>
> Actually, Oracle does 2 commits, one each before and after the DDL.
>
> Part of why Oracle is different than other DBMS engines is its
> architecture - this is well explained in the book hpuxrac suggested.
>
> 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.
>
> If you are going to see how sausage is made, bring along a barf bag.
>
> jg
> --
> @home.com is bogus.
> Awright, what dumbass DBA is pissing people off...
> http://www.signonsandiego.com/news/nation/20081110-1037-baptizingthedead.html

Hear hear!

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Nov 11 2008 - 18:08:06 CST

Original text of this message