Re: Estimating rollback segment size

BS wrote:
> Group,
>
> Is there a way to estimate the size of rollback segment based on an
> update statement? I have to update a table that has over 46K rows and
> the update will be a join between this and another table which has 2260
> rows. I will be updating the 2 columns of the larger table and both of
> these are a varchar2 column.
>
> The current size of the rollback segment is 1500 MB and when I try to
> run the update, it runs out of rollback segment space with the error
> below
>
> ERROR at line 1:
> ORA-01562: failed to extend rollback segment number 7
> ORA-01650: unable to extend rollback segment RB7 by 515 in tablespace
> RBS
>
> Thanks

Hi,

Are you sure you don't have other transactions running? 46K rows that will have ~350MB (at most) updated shouldn't generate that much undo.

I assume you are on 8i since you are using rollback segments. You can check v$transaction, as since at least that release, it has columns for the number of blocks a transaction is using.

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76961/ch3175.htm#11192

Regards,

Steve Received on Thu Aug 24 2006 - 10:07:37 CDT