RE: simple question on DDL

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: Deshpande, Kirti
  INET: kirti.deshpande_at_verizon.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).