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.

Peter, I believe that the first thing you should do is determine how big a rollback segment would be required to handle the single insert into transaction and compare that to how much total space you have available for use as rollback area.

If your total rbs tablespace is less than this number then you need to change your process to deal with chuncks of data or add more data files to begin with.

Second, 320K, is a very small rbs segment extent size and is definitely not an appropriate extent size for a task dealing in gigabytes. In traditional rbs segment management the allocation of an extent is a relatively expensive process so the less times your task has to allocate an extent the better.

If you have enough total area already then you should drop your existing rbs segments and reallocate one large segment to handle the job. But doing this probably means that this is a maintenance window task. Also if this is not a one time deal then you either need to allocate a great deal more rollback, change the process, or both.

If this is a one-time deal then allocating a "temporary" rbs tablespace to hold one very large segment created with very large extents and dropping it when done may be your best option. This is particulary true if you must accomplish this task while the system is in use by normal processing.

Also remember that the index updates on the target also take rbs space so if you have reason to believe that you have just about enough space to perform the table inserts as one step then dropping the indexes before doing the insert and rebuilding them afterwords might be an option. But I would hate to have to resort to rebuilding large indexes just because I was a few megabytes short of rbs space, but depending on how you data is derived and other restrictions you have not mentioned it may be an option. However, the time to rebuild may make this option very undesirable.

Just some ideas. You are bound to receive numerous reponses on rbs segments.

• Mark D Powell --