Re: Rollback extents

Hi Peter,

Assuming you are not performing the insert in parallel, the insert transaction will be assigned to the one RBS so adding more won't help. You need to ensure that one will be big enough to cope (either increase extent size or maxextents or both) and that it gets assigned to the transaction (either with the set transaction statement or putting offline all the other RBSs).

If you're on 9i (?), the resumable option is very useful in situations such as this and might be worth checking out. It basically allows the transaction to remain suspended until the RBS has been increased.

Good luck.

Richard

"PeterS" <member_at_dbforums.com> wrote in message news:3d195c43$1_at_usenetgateway.com...
> Hello, everybody. I am working on creating big set of data (~3-8 GB). I
> am using "insert into ... select" statement. I have 4 rollback segments
> with 500 maxextents each. Initial extent =320K, next extent =320K. Here
> is dynamic info:
>
> Rollback Segment Size (KB) Gets Waits % Waits # Shrinks # Extends
> ---------------- ---------- ------------ ---------- ------- ---------
> --------- SYSTEM 4,792 44,146 0 0.00 0 58 RBS01 6,392 97,516 0 0.00 0 0
> RBS02 6,392 96,732 0 0.00 0 0 RBS03 6,392 97,172 0 0.00 0 0 RBS04
> 159,992 132,030 0 0.00 0 480
>
> I inserted ~200000 rows and got the following:
>
> ORA-01562: failed to extend rollback segment number 5 ORA-01628: max #
> extents (500) reached for rollback segment RBS04
>
> I wouldn't like to set maxextents unlimited, since my disk space is
> limited. Could anybody suggest something? Should I increase extents? Or
> number of rollback segments?
>
> I appreciate any help! Thank you. Peter.
>
>
>
> --
> Posted via dBforums
> http://dbforums.com
Received on Wed Jun 26 2002 - 03:06:48 CDT