RE: simple question on DDL

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