Re: Rollback extents

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.

That is why I prefer using PL/SQL for inserting. Even though it is about 3 times slower than direct insert ... as select, it makes rollback segment much easier to handle, and best of all, even if it fail, it only roll back a small amount, not the whole process.

One other thing i want to point is, if another schema is selecting against the same table you are inserting, it will be extremely slow, because it will look through the huge rollback segment.  

-- 
Sent by joebayerii  from hotmail subdomain of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/new