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:
>

When your SQL statement fails everything will be rolled back. How do you know you only inserted 200000 rows? This number doesn't explain the fact that you used 160MB undo space.

> 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?
>

Do you have large indexes on this table, or triggers which would cause other tables to be updated? If so, you know you got a workaround. If you are inserting into an empty table then there is an easy solution: drop table <table_name>;
create table <table_name> as select ...;

"create table" won't need undo for the data itself. The only undo space (usually no more than a few undo block) it needs is for updating the system catalog.

If it neither of the above senarios you just have to make your rollback segment larger or process your data in batches.

> I appreciate any help! Thank you. Peter.
Received on Thu Jun 27 2002 - 21:52:08 CDT