It could also have something to do with the status changes of objects that may be affected by the DDL, though I am speculating here.
Jared On Thursday 23 January 2003 02:58, you wrote: > remember this "functionality" of committing before a DDL statement has > been around from the beginning. Autonomous transactions have not. It > may simply be a case of Oracle not getting around to adding that change > to the kernel code. > > Or, as Kirti quoted from Tom Kyte, that might just be the way they want > it to work. > > --- Arup Nanda <[EMAIL PROTECTED]> wrote: > > Dan, > > > > If I may, essentially you are saying that changes to data dictionary > > tables > > have to be committed immediately regardless of the outcome of the > > transaction. > > > > For instance in the following code, starting with an empty table t1 > > > > step 1: insert into table t1 values row1 > > step 2: create table t2 > > step 3: insert into table t1 values row2 > > step 4: rollback > > > > At this point a select * from t1 will show only row1, since the ddl > > create > > table t2 has inserted a commit. However, the point is, my transaction > > should > > have been from step 1 through step 4, not fromn step 3 through 4. The > > DDL > > broke my txn at step 2 and another transaction started from there. > > The data > > dictionary tables were updated and they should be committed; but that > > commit > > could have been done via an "autonomous transaction", not in the same > > > > transaction the user issued. > > > > The more I think about it, I see no point why a DDL should insert a > > commit. > > This is different from saying that DDL itself may issue a commit to > > its > > seprate transaction to update the catalog. Any thoughts on that? > > > > Arup > > > > >From: "Fink, Dan" <[EMAIL PROTECTED]> > > >Reply-To: [EMAIL PROTECTED] > > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > >Subject: RE: simple question on DDL > > >Date: Wed, 22 Jan 2003 14:18:57 -0800 > > >MIME-Version: 1.0 > > >Received: from newsfeed.cts.com ([209.68.248.164]) by > > >mc1-f3.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Wed, > > > > 22 Jan > > > > >2003 15:13:04 -0800 > > >Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com > > >(8.9.3/8.9.3) with UUCP id PAA16552;Wed, 22 Jan 2003 15:11:42 -0800 > > > > (PST) > > > > >Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id > > > > 00537F3B; > > > > >Wed, 22 Jan 2003 14:18:57 -0800 > > >Message-ID: <[EMAIL PROTECTED]> > > >X-Comment: Oracle RDBMS Community Forum > > >X-Sender: "Fink, Dan" <[EMAIL PROTECTED]> > > >Sender: [EMAIL PROTECTED] > > >Errors-To: [EMAIL PROTECTED] > > >Organization: Fat City Network Services, San Diego, California > > >X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. > > > > Bergman > > > > >Precedence: bulk > > >Return-Path: [EMAIL PROTECTED] > > >X-OriginalArrivalTime: 22 Jan 2003 23:13:04.0174 (UTC) > > >FILETIME=[D0E4CCE0:01C2C26B] > > > > > >Don't forget that extent allocation also affects the extent map for > > > > the > > > > >segment and possibly the high water mark. The hwm can be set without > > >allocating another extent and allocation of an extent may not alter > > > > the hwm > > > > >(if you manually allocate an extent). If I deallocate space from an > > > > object, > > > > >I will alter the rows in fet$ and uet$ but not update the hwm. Make > > > > sense? > > > > >As for the ATOMICITY of the transaction, this is usually used to > > > > describe > > > > >the changes to data of interest. I don't think it is used to > > > > describe any > > > > >underlying data dictionary changes. Thus the answer is Yes (for 99% > > > > of the > > > > >Oracle techies) and No (for the 1% of us who really like to know > > > > exactly > > > > >what is going on under the covers). > > > > > >Thanks for a great question, it brought up a subject that I had > > > > never > > > > >thought about. Yee-Haw! I learned someting today! > > > > > >Cheers, > > >Dan > > > > > >-----Original Message----- > > >Sent: Wednesday, January 22, 2003 2:04 PM > > >To: Multiple recipients of list ORACLE-L > > > > > > > > > > > >Thanks Dan. The gist of your response was that all changes to the > > > > data > > > > >dictionary are immediately commited. Seems to make sense to me. > > > > Maybe, > > > > >thats one reason why one cannot free space below the high water > > > > mark. Coz > > > > >changes to UET$ has been committed, even though the data was rolled > > > > back. > > > > >I sent an email to one of my senior DBA friends, posing the same > > > > question, > > > > >and he replied with a one liner "To make the transaction as ATOMIC > > > > as > > > > >possible - They either run completely, or not at all". Now, does > > > > that mean > > > > >the Insert, update and delete statements are not ATOMIC? For on a > > > > rollback, > > > > >changes to the data dictionary are commited, whereas the data is > > > > rolled > > > > >back. > > > > > >Thanks > > >Raj > > > > > > > > > > > > > > > > > > > > > "Fink, Dan" > > > > > > <Dan.Fink@mdx To: Multiple > > > > recipients of > > > > >list > > >ORACLE-L <[EMAIL PROTECTED]> > > > .com> cc: > > > > > > Sent by: Subject: RE: simple > > > > question > > > > >on > > >DDL > > > root@fatcity. > > > > > > com > > > > > > > > > > > > > > > > > > January 22, > > > > > > 2003 02:16 PM > > > > > > Please > > > > > > respond to > > > > > > ORACLE-L > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >Take the case of an insert (we'll call tx1), where space allocation > > > > is > > > > >required. As you insert records, the table allocated additional > > > > extents > > > > >(updating fet$ (free extent table) and uet$ (used extent table) in > > > > the data > > > > >dictionary). These updates to the data dictionary are implicitly > > > > committed, > > > > >even if you issue an explicit rollback for the insert statement. > > > > Imagine if > > > > >the dd changes are not immediately committed. Let's say another tx > > > > (we'll > > > > >call tx2) needs to allocate an extent in the same datafile. If fet$ > > >contains only a single row for the file requested, then tx1 will > > > > have an > > > > >exclusive lock on the row. tx2 needs to also lock the row > > > > exclusively in > > > > >order to update it. Thus, tx2 would wait until tx1 has completed and > > >released the lock. In the meantime, any transaction that needs to > > > > allocate > > > > >a > > >new extent in that file will have to wait...and wait...and wait. > > > > > >A different strategy is to commit the changes to fet$ and uet$ > > > > immediately. > > > > >Then the next tx can access the row and grab space. While this could > > > > result > > > > >in an overallocation of space if the tx is rolled back, it does not > > > > block > > > > >other txs. If space was allocated to an object, and the tx failed, > > > > there is > > > > >a strong probability that this space will be used at some point in > > > > the > > > > >future. > > > > > >It seems that the tradeoff here is that the access to the data > > > > dictionary > > === message truncated === > > > __________________________________________________ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).