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

On 11 Nov, 02:31, zigzag..._at_yahoo.com wrote:
> On Nov 10, 5:27 pm, zigzag..._at_yahoo.com wrote:
>
>
>
> > On Nov 10, 4:19 pm, joel garry <joel-ga..._at_home.com> 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-baptizingthed...
>
> > 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.

Yes, this demonstrates that you can rollback a creation of a table. I'm just curious to
know the implementation and the impact on the system of such a beautiful feature.
Does it support the correct isolation level? What appens when you add or remove
a column from a table with a lot of data and then you rollback?? You do it on live system (and that, as said here by others is a questionable thing)
because you cannot stop a live system for the upgrade but ... ooops you've made
and error and you lock the system for a lot of time waiting a rollback ...

MS SQL Server introduced the possibility to not lock readers for read committed
isolation level with 2005 version, so i'm sceptic about the efficent implementation
of transactional DDL.
Maybe my mind is to closed.

Regards,
 Cristian Received on Tue Nov 11 2008 - 02:25:40 CST