RE: Some rollback doubts

Thanks KG, for your reply. Well, it whets my appetite for more.

This surprises me. All the documentation I have read so far tells me that multiple transactions cannot be write to the same rollback segment data block.

My first question was basically "well, if many transactions can write to an extent, then when a transaction "wraps" around to the first extent, why does it not write to the first extent if it has another active transaction?". I sort of found an answer to this at asktom: "A transaction can be allocated space in an extent that is also used by other transactions. The prohibition against entering an extent that has active update transactions in it applies only when the extent boundaries are crossed". Now, why is this prohibited?

As to the free extent pool, when do blocks get assigned and deallocated from this free pool? No pctfree algorithm here??? And if Oracle always cycles back to the first extent after writing to the last, would this free extent pool always have a block belonging to the first extent in it? From your reply, I deduce that the answer to my second question is that , my transaction could get assigned to any block in the free extent pool. This block could belong to the 1st extent or the 20th extent. ???

The reasons for this questions are:

I have one huge rollback segment, with extents sized at 500M each, with a possible maximum of 22 extents. A batch job has been failing with an "Unable to extend rollback segment" error. I find that there are users executing select statements across a link when this batch job is running. This select takes a TX lock on the rollback segment, and uses 1 block of undo space. The users never commit or rollback this select transactions. So, if theres one user executing a select and using the first extent of the rollback segment, then if my batch job started with writing into the 21st extent, it would only have 2 extents to write to. Thats about 10 Gb of free space that cannot be used.

Regards
Raj

                                                                                       
                            
                    "K                                                                 
                            
                    Gopalakrishnan       To:     Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>       
                    "                    cc:                                           
                            
                    <[EMAIL PROTECTED]       Subject:     RE: Some rollback doubts     
                                
                    o.com>                                                             
                            
                    Sent by:                                                           
                            
                    [EMAIL PROTECTED]                                                  
                               
                    ity.com                                                            
                            
                                                                                       
                            
                                                                                       
                            
                    06/12/2003                                                         
                            
                    01:55 PM                                                           
                            
                    Please respond                                                     
                            
                    to ORACLE-L                                                        
                            
                                                                                       
                            
                                                                                       
                            

Rajesh:

I am not sure which document you are referring here. If that documentation says more than one transaction can not use a rollback segment data block means, I would say the documentation is incorrect in this case. But this can not happen concurrently. IT can happen serially.

Each rollback segment's header will have something called free extent pool, which will link at least 5 undo segment blocks which have more than 400 bytes
of free space. These blocks will be used to store undo information for the subsequent transactions. This 'free extent pool' will be clearly visible in the segment header dumps.

You can dump the rollback segment header by using the alter system dump undo header 'undo_segment_name' command. However this free extent pool just keep 5 undo blocks with free space. I hope this answers both of your questions..

Best Regards,
K Gopalakrishnan

-----Original Message-----
[EMAIL PROTECTED]
Sent: Thursday, June 12, 2003 10:00 AM
To: Multiple recipients of list ORACLE-L

>From the docs : Many active transactions can write concurrently to a single
rollback segment--even the same extent of a rollback segment; however, each data block in a rollback segment's extent can contain information for only a single transaction.

1. Now, when oracle writes to the last extent in the cycle, and sees that the first extent has an active transaction, it allocates a new extent. Well, why should it? Cant it write to the first extent?
2. When a new transaction needs to use the rollback segment, and say it gets allocated to a rollback segment with a few other transactions, how does it decide which extent to use from the cycle of extents?

Thanks
Raj

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
  INET: [EMAIL PROTECTED]


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).