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:Dan.Fink_at_mdx.com]
> 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" <Dan.Fink_at_mdx.com>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >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_at_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: <F001.00537F3B.20030122141857_at_fatcity.com>
> >X-Comment: Oracle RDBMS Community Forum
> >X-Sender: "Fink, Dan" <Dan.Fink_at_mdx.com>
> >Sender: root_at_fatcity.com
> >Errors-To: ML-ERRORS_at_fatcity.com
> >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: root_at_fatcity.cts.com
> >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_at_mdx To: Multiple
> recipients of
> >list
> >ORACLE-L <ORACLE-L_at_fatcity.com>
> > .com> cc:
> >
> > Sent by: Subject: RE:
> simple question
> >on
> >DDL
> > root_at_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: Rajesh.Rao_at_jpmchase.com
> >
> >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: ListGuru_at_fatcity.com (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: Dan.Fink_at_mdx.com
> >
> >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: ListGuru_at_fatcity.com (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: arupnanda_at_hotmail.com
>
> 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: ListGuru_at_fatcity.com (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: Dan.Fink_at_mdx.com
>
> 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: ListGuru_at_fatcity.com (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: MGogala_at_oxhp.com
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: ListGuru_at_fatcity.com (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).
Received on Thu Jan 23 2003 - 10:44:33 CST
