RE: simple question on DDL

I tend to agree with the argument "that's the way they wrote it". If I remember correctly, DDL always commits the current transaction in the session, even back in Oracle5 [and earlier ?] days. Most likely they couldn't write autonomous transactions then. That "autonomous transactions" have been available to us since 8i doesn't mean that autonomous transactions may not have been possible within the kernel earlier. I always think that the SCN mechanism is like a sequence and has been around since before Oracle6 but sequences were available to us only in Oracle6.

Hemant

At 06:54 AM 23-01-03 -0800, you wrote:

>Well, then even Tom's vague on this one.
>
>"DDL locks are automatically placed against objects during a DDL operation
>to protect them from changes by other sessions".
>Well, then even an insert statement makes changes to DD objects. Ain't
>locks taken then?
>
>"DDL Starts by commiting".
>Why not declare a savepoint, and later, rollback to that savepoint?
>
>And theres another DBA friend, who speculates that the reason could be that
>DDL statements always take an exclusive lock on the underlying object. If
>that transaction is not ended implicitly, the locks would be waiting
>forever until the user intervenes. This could have serious consequences on
>the database, with a potential for deadlocks. Ahem. This by far, seems to
>be the most convincing answer to me.
>
>Ok. Maybe, thats just the way its coded. The point I have understood from
>this thread, is that a commit is performed to protect the data dictionary.
>And DML statements are not truly atomic, for while it maybe possible for an
>DML statement to roll back, there are some changes that are indeed
>committed.
>
>Thanks
>Raj
>
>
>
>
>
>
> "Deshpande,
> Kirti"
>
> <kirti.deshpande_at_ve To: Multiple
> recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> rizon.com> cc:
>
> Sent by: Subject: RE: simple
> question on DDL
> root_at_fatcity.com
>
>
>
>
>
> January 22,
> 2003
>
> 10:33
> PM
>
> Please respond
> to
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
>Dan addressed this very well in his earlier post...
>
>Here is what Tom Kyte says in his book (Expert one-on-one Oracle):
>"DDL locks are automatically placed against objects during a DDL operation
>to protect them from changes by other sessions".....
>"DDL locks are held for the duration of the DDL statement, and are released
>immediately afterwards. This is done, in effect, by always wrapping DDL
>statements in implicit commits (or commit/rollback pair). It is for this
>reason that DDL always commits in Oracle.".....
>"So, DDL will always commit, even if it is unsuccessful. DDL starts by
>committing - be aware of this. It commits first so that if it has to
>rollback, it will not roll back your transaction. If you execute DDL, it'll
>make permanent any outstanding work you have performed, even if the DDL is
>not successful.".....
>
>
>Refer to page 119...
>
>- Kirti
>
>-----Original Message-----
>Sent: Wednesday, January 22, 2003 8: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
>
>
>--
>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).

Hemant K Chitale
My web site page is : http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

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