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).
