Re: Why Oracle does not allow rollback of DDL statements?
Date: Tue, 11 Nov 2008 00:54:52 -0800 (PST)
On 11 Nov., 09:25, Cristian Cudizio <cristian.cudi..._at_yahoo.it> wrote:
> 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??
Any other transaction could affect a lot of data. What's the point here? Received on Tue Nov 11 2008 - 02:54:52 CST