RE: simple question on DDL

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