Re: simple question on DDL

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 <arupnanda_at_hotmail.com> 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" <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
>
> === 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: jkstill_at_cybcon.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).