Path: news.cambrium.nl!textnews.cambrium.nl!feeder3.cambrium.nl!feed.tweaknews.nl!postnews.google.com!s9g2000prg.googlegroups.com!not-for-mail From: Cristian Cudizio Newsgroups: comp.databases.oracle.server Subject: Re: Why Oracle does not allow rollback of DDL statements? Date: Tue, 11 Nov 2008 01:37:27 -0800 (PST) Organization: http://groups.google.com Lines: 143 Message-ID: <6ef3cf9e-394a-4e48-93c3-bc1c7c809309@s9g2000prg.googlegroups.com> References: <57ce7648-b6db-4e34-ad52-4dc171ceb0a9@v16g2000prc.googlegroups.com> <6df356db-abc4-4a20-8267-aac817c6025a@a26g2000prf.googlegroups.com> <7b2cf7c0-f69f-4eaf-a7c4-fc8e356f80ae@i24g2000prf.googlegroups.com> NNTP-Posting-Host: 88.42.187.162 Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1226396247 14926 127.0.0.1 (11 Nov 2008 09:37:27 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Tue, 11 Nov 2008 09:37:27 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: s9g2000prg.googlegroups.com; posting-host=88.42.187.162; posting-account=-XoqjQoAAAD7QdHXOxDkJRXFXwHVaYFJ User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; it; rv:1.9.0.3) Gecko/2008092417 Firefox/3.0.3,gzip(gfe),gzip(gfe) Xref: news.cambrium.nl On 11 Nov, 09:54, steph wrote: > On 11 Nov., 09:25, Cristian Cudizio wrote: > > > > > On 11 Nov, 02:31, zigzag...@yahoo.com wrote: > > > > On Nov 10, 5: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 stat= ements > > > > > > simply write information (insert, delete and update) to data > > > > > > dictionary, so why DDL statement cannot be rolled back. Am I mi= ssing > > > > > > 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 r= ollback > > > > > > of DDL satements. Rollback of all staements should be allowed n= ot just > > > > > > of DML statements. > > > > > > > Appercaite your insight. > > > > > > Actually, Oracle does 2 commits, one each before and after the DD= L. > > > > > > Part of why Oracle is different than other DBMS engines is its > > > > > architecture - this is well explained in the book hpuxrac suggest= ed. > > > > > > I don't know the real reason you can't rollback DDL, but I suspec= t > > > > > part of it is simply the evolution of the code. =A0By now, it wou= ld be > > > > > difficult. =A0And there are workarounds in the modern versions. > > > > > > As far as Serge's assertion that customers want to be able to cha= nge > > > > > 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 t= hat's > > > > > no reason to make it easy at the expense of other, more reasonabl= e and > > > > > ordinary things. =A0I wonder how such things don't violate Codd's > > > > > Physical Data Independence rule - they logically impair applicati= ons. > > > > > > Just because there is a Codd rule that states the same language m= ust > > > > > 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 b= ag. > > > > > > jg > > > > > -- > > > > > @home.com is bogus. > > > > > Awright, what dumbass DBA is pissing people off...http://www.sign= onsandiego.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; yo= u > > > > 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 w= rite > > > > 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 w= as > > > > 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 immediat= ely > > > > think that everything in database can be rolled back. When they fin= d > > > > that their database cannot rollback DDL, they are really surprised. > > > > Having worked in Oracle for many years, I thought no relation datab= ase > > > > 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. > > > =A0I then did > > > begon transaction > > > create table t1 > > > commit transaction > > > > and table t1 was created. > > > > I, therefore, do not undestand why people in this =A0forum are saying > > > SQL Server does not allow it but tools do. > > > SQL Sever engine is the one which is executing SQL Satements and it i= s > > > 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? =A0What appens when you ad= d > > 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? Yes in fact i consider massive changes of data as part of deployment procedure (but now i'm doing and example of my experience with an OLTP system) that needs a scheduled downtime. Regards, Cristian