Actually, DB2 (pardon my French) doesn't issue a commit after a DDL. I'm not aware of any standards specifying the presence or absence of a "commit" after a DDL. I know that, for instance, "EXPLAIN PLAN" can be rolled back.
> -----Original Message----- > From: Fink, Dan [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 23, 2003 10:05 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: simple question on DDL > > > Arup, > I see your point and agree that the DDL should be an > autonomous tx. > Perhaps an enhancement request is in order? Since Oracle has > the autonomous > tx code, integrating into the kernel should be > considered...perhaps for > Oracle 38i? > In the absence of that change, I must disagree and say > that ddl must > issue a commit. If you look at a combination of your example > and mine, the > problem of waiting transactions still exists. In order to > create a table, > space must be allocated. Again, if fet$ contains only 1 row for the > particular file where the table is to be created, there is a > potential for a > serious locking problem. > > Dan > > -----Original Message----- > Sent: Wednesday, January 22, 2003 7:14 PM > To: Multiple recipients of list ORACLE-L > > > 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 > >is > >kept to a minimum duration at the expense of periodic space wastage > >(initially). > > > >As for the other data dictionary tables, it may be a case of read > >consistency conflicts. If a long running transaction needs > to access a > >table > >definition, but a previous transaction has updated the table > definition, > >but > >not committed, which table definition do you use? There are > some issues > >with > >definitions not being the same at the start of a transaction > and at a later > >point. As I recall, Oracle terminates the transaction if > object definitions > >change while the transaction is running. > > > >All in all, it makes sense (at least to me) that changes to the data > >dictionary are immediately committed. Otherwise, the performance and > >integrity of the system would be comprimised. > > > >Dan Fink > > > >-----Original Message----- > >Sent: Wednesday, January 22, 2003 10:01 AM > >To: Multiple recipients of list ORACLE-L > > > > > > > >That raises another doubt. For an simple insert statement, could also > >update the UET$ or FET$ tables? So, if the purpose was to > preserve all > >changes to the data dictionary, What's different between > OBJ$, COL$ and > >these space management tables? > > > >Thanks > >Raj > > > > > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.net > >-- > >Author: > > 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). > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.net > >-- > >Author: Fink, Dan > > 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). > > > _________________________________________________________________ > MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. > http://join.msn.com/?page=features/virus > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Arup Nanda > 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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Fink, Dan > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen 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).
