Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambrium.nl!feed.tweaknews.nl!postnews.google.com!n33g2000pri.googlegroups.com!not-for-mail From: joel garry Newsgroups: comp.databases.oracle.server Subject: Re: Why Oracle does not allow rollback of DDL statements? Date: Tue, 11 Nov 2008 10:47:40 -0800 (PST) Organization: http://groups.google.com Lines: 88 Message-ID: <5d61eb10-7c10-40da-ba1e-cbe31d9b32c3@n33g2000pri.googlegroups.com> References: <57ce7648-b6db-4e34-ad52-4dc171ceb0a9@v16g2000prc.googlegroups.com> NNTP-Posting-Host: 75.49.200.201 Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1226429260 15534 127.0.0.1 (11 Nov 2008 18:47:40 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 11 Nov 2008 18:47:40 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: n33g2000pri.googlegroups.com; posting-host=75.49.200.201; posting-account=tpQovAkAAABNoH5bwsZAiff2L0zxGwdv User-Agent: G2/1.0 X-HTTP-Via: 1.0 ISA2K4-OC1 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; InfoPath.1),gzip(gfe),gzip(gfe) Xref: news.cambrium.nl On Nov 10, 2:27=A0pm, zigzag...@yahoo.com wrote: > On Nov 10, 4:19=A0pm, joel garry wrote: > > > > > > > On Nov 9, 3:36=A0pm, zigzag...@yahoo.com wrote: > > > > I have always been surprised why Oracle does a implicit commit when > > > DDL statements (e.g create table =85) are executed. =A0DDL 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 Serve= r > > > does allow rollback of DDL statements if I am not mistaken. It lloks > > > like there is something in Oracle architecture which prevents rollbac= k > > > of DDL satements. Rollback of all staements should be allowed not jus= t > > > 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. =A0By now, it would be > > difficult. =A0And 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. =A0There 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. =A0I 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.signonsand= iego.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. =A0If 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/200= 81109/news_1n9russub.html