Re: Fail to extend rollback segment ?

WANG SHIANG HUEY wrote:
>
> Hi,
> when I insert 200,000 records into a table from a query, I encounter
> the following errors.
>
> ORA-01562: failed to extend rollback segment (id = 1)
> ORA-01628: max # extents (121) reached for rollback segment RB_TEMP
>
> I have added a datafile size 20M (total is 35M)
> and increase the initial to 1M and next to 500K on ROLLBACK_DATA
> tablespace, but it didn't solve the
> problem. The number of extents is limited by db_block_size, so max
> I can have is 121. I may be thinking in a wrong direction.
> Please advise, thanks !
> --
> Shiang-Huey Wang
> Leather Center, Inc.
> sherry_at_leathercenter.com
> wshiang_at_a.cs.okstate.edu

Yes, it means there you hit maxextents limit. And yes, you need to change size of extents as you are hitting hard limit for 2Kb block. But:
Did you recreate rollback segment after changing default storage settings for rollback tablespace? Note that when you creating the rollback segments, the storage paramas are taken from tablespace unless defined. So, your change to the tablespace storage will afect ONLY NEW ROLLBACK SEGMENTS created after modification, it will NOT afect existing rollback segments.  Try following:
1. after you change rolback tablespace storage , create new or drop and recreate existing rollback segment
2. make sure your session is using created rollback segment

    ( set transaction use rollback segment rollback_name ) 3. do your insert

Should work.
Halina Monka Received on Fri Oct 18 1996 - 00:00:00 CEST