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

On Nov 10, 2: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.

You might want to google for ORA-00054, then note http://www.oracle.com/technology/deploy/availability/htdocs/online_ops.htm

Once again, it boils down to, there's lots going on, you can't assume everyone wants their DDL to be invalidated in the middle of what they are doing, or that everyone is doing the same thing.

Not knowing whether it is going to work in SQL Server, is worse.

jg

--
@home.com is bogus.
Always mount a scratch monkey.  http://www.signonsandiego.com/uniontrib/20081109/news_1n9russub.html