Re: Sizing Rollbacks segments

In article <867ltqo2c3.fsf_at_bandol.concept.fr>,   alawa_at_bandol.concept.fr wrote:
>
>
> Rollback segments are supposed to hold the modifications
> that occurs on a transaction until the commit.
> So, theoricaly we sould be able to predict
> the size of the rbs needed for a simple "delete table ..".
>
> Oracle support says "No, there is no rules to calculate this
> size", wich is not satisfying.
>
> We tried a simple test with a table+index of 23Mo and a single
> rbs of 30M. The "delete table" ends with the ORA-1562 error.
>
> How much space do I need ? 2 times the amount of data, 10 times ?
>
> Thank's for any pointer.
>
> AB
> alawa_at_concept.fr
>

The answer is it depends. What it depends on is the number of indexes, and the distribution of the deleted rows. The first time you delete or change a row in a block the entire block is copied to rbs. The next change in a block results in vector information being copied. If the indexes on the table are affected by the update/delete then index blocks also are copied to rbs so the amount of space required varies from one statement to the next.

Note a 01562 is an 'unable to extend' which means that Oracle was unable to find the space to allocate another extent. You may need to defrag your rbs tablespace or to add another data file if the total space is too small to support the average amount of data that is being changed on your system. Have you set the optimal parameter for your rollback segments?

Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Feb 10 1999 - 08:56:29 CST