Re: Help, rollback segment failed to extend.

From: Kenneth C Stahl <BluesSax_at_Unforgettable.com>
Date: Wed, 11 Aug 1999 09:04:14 -0400
Message-ID: <37B174CE.AC04036C@Unforgettable.com>

Just out of curiosity how about these for ideas:

Do you have a rollback segment other than SYSTEM defined in your System tablespace? If so, was it offline when you did the transaction you describe?
While you let your rollback segments expand to 500 extents, the RBS tablespace is only 200M. If you have 3 rollback segments with an extent size of 1M and minextents of 15 that means that even with no transactions 45M of your 200M is tied up just in initial extents. That leaves only 155M left so once you reach 170 extents you've exhausted available space in the tablespace.

Peter Laursen wrote:

> While doing service at a customers database I wanted to delete 1.4 millions
> rows from a table and failed because the rollbacksegment failed to extend.
> I had to delete in batches of 50.000 commiting after each batch, even
> deleting 100.000 failed. The rowsize is 146 bytes. The table has 3 indexes,
> one pk and two nonuniques. No other users/sessions were active. At home, on
> a database with the same setup, experiments showed no problems.
> These were the errormessages I got:
> ORA-01562: failed to extend rollback segment ID = num
> ORA-01628: max # of extents num reached for rollback segment num
> System: Oracle 8.05 on NT4 sp3.
> The database is part of an application. When in production the db runs
> unattended at the customer. I make scripts for db-creation and all
> customers use the same setup.
> Here is part of the scripts concerning rbs:
>
> create tablespace rbs
> datafile '<path>\rbs01.dbf' size 200m
> default storage (initial 1m next 1m maxextents unlimited
> minextents 15 pctincrease 0);
>
> create public rollback segment rbs1
> tablespace rbs
> storage(initial 1m next 1m minextents 15 maxextents 500 optimal 15m);
>
> create public rollback segment rbs2
> tablespace rbs
> storage(initial 1m next 1m minextents 15 maxextents 500 optimal 15m);
>
> create public rollback segment rbs3
> tablespace rbs
> storage(initial 1m next 1m minextents 15 maxextents 500 optimal 15m);
>
> Now my questions:
> What could have made the deletes fail at the customer database?
> What should I look for to find the reason?
> Is the the rbs setup described above bad in some way?
>
> Thanks
> Peter
Received on Wed Aug 11 1999 - 08:04:14 CDT