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

From: <>
Date: Mon, 10 Nov 2008 17:31:15 -0800 (PST)
Message-ID: <>

On Nov 10, 5:27 pm, wrote:
> On Nov 10, 4:19 pm, joel garry <> wrote:
> > On Nov 9, 3:36 pm, 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
> > --
> > is bogus.
> > Awright, what dumbass DBA is pissing people off...
> There are many legitimate reasons you want to have transaction ddl,
> once you learn database you are using does not have the feature; you
> have to live with work around.
> For examples, I am developing installation script to create schema
> changes
> for new release of system.  If there was transaction DDL, I can write
> all the changes and in case of error, roll them back. In absence of
> transactional DDL, I have to write the script: if a schema change was
> already made do not make it, otherwise make the change.
> Database is the only thing I know in Computer Science, which has
> concept of transaction where by a group of statements can be rolled
> back in case of error. When students learn databases, they immediately
> think that everything in database can be rolled back. When they find
> that their database cannot rollback DDL, they are really surprised.
> Having worked in Oracle for many years, I thought no relation database
> provides this feature. When I started learning SQL Server, I was
> pleasantly surprised that SQL Server can do it.- Hide quoted text -
> - Show quoted text -

I am using SQL SERVER 2000. I went in Query Analyzer. I did

begin transaction
create table t1 ..
rollback transaction

Table t1 was not created.
 I then did
begon transaction
create table t1
commit transaction

and table t1 was created.

I, therefore, do not undestand why people in this forum are saying SQL Server does not allow it but tools do. SQL Sever engine is the one which is executing SQL Satements and it is allowing rollback.

Which part of SQL SERVER does not allow transact SQL. Received on Mon Nov 10 2008 - 19:31:15 CST

Original text of this message