Good point about the autonomous transactions. If I remember correctly that was new
in 8i.
My general theory on WHY questions is "That's the way they coded it!". They
probably could have coded it differently, but they didn't. The important thing is that
it is well documented how it works. If someone mixes ddl and dml they have no basis
to complain about the result.
John
[EMAIL PROTECTED] 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? ArupFrom: "Fink, Dan" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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 Jan2003 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 id00537F3B;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.BergmanPrecedence: 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 forthesegment and possibly the high water mark. The hwm can be set without allocating another extent and allocation of an extent may not alterthe hwm(if you manually allocate an extent). If I deallocate space from anobject,I will alter the rows in fet$ and uet$ but not update the hwm. Makesense?As for the ATOMICITY of the transaction, this is usually used todescribethe changes to data of interest. I don't think it is used todescribe anyunderlying data dictionary changes. Thus the answer is Yes (for 99%of theOracle techies) and No (for the 1% of us who really like to knowexactlywhat is going on under the covers). Thanks for a great question, it brought up a subject that I hadneverthought 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 thedatadictionary are immediately commited. Seems to make sense to me.Maybe,thats one reason why one cannot free space below the high watermark. Cozchanges to UET$ has been committed, even though the data was rolledback.I sent an email to one of my senior DBA friends, posing the samequestion,and he replied with a one liner "To make the transaction as ATOMICaspossible - They either run completely, or not at all". Now, doesthat meanthe Insert, update and delete statements are not ATOMIC? For on arollback,changes to the data dictionary are commited, whereas the data isrolledback. Thanks Raj "Fink, Dan" <Dan.Fink@mdx To: Multiplerecipients oflist ORACLE-L <[EMAIL PROTECTED]> .com> cc: Sent by: Subject: RE: simplequestionon 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 allocationisrequired. As you insert records, the table allocated additionalextents(updating fet$ (free extent table) and uet$ (used extent table) inthe datadictionary). These updates to the data dictionary are implicitlycommitted,even if you issue an explicit rollback for the insert statement.Imagine ifthe dd changes are not immediately committed. Let's say another tx(we'llcall tx2) needs to allocate an extent in the same datafile. If fet$ contains only a single row for the file requested, then tx1 willhave anexclusive lock on the row. tx2 needs to also lock the rowexclusively inorder to update it. Thus, tx2 would wait until tx1 has completed and released the lock. In the meantime, any transaction that needs toallocatea 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 couldresultin an overallocation of space if the tx is rolled back, it does notblockother txs. If space was allocated to an object, and the tx failed,there isa strong probability that this space will be used at some point inthefuture. It seems that the tradeoff here is that the access to the datadictionary=== message truncated === __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com
