Re: RBS tablespace advise needed

This is one of those "it depends" questions.

If you are running out of space in your RBS tablespace (and I presume that the only objects in that tablespace are rollback segments) then you may need to expand the size of the datafile.

If you are using 7.3 or above then this is easy using the command: alter database datafile 'xxxxx' resize ??M

where xxxx is the name of the file and ?? is the size you want to take it to.

The other thing to check is whether you have set OPTIMAL on your rollback segments so that they shrink back to some predetermined size once they have grown beyond that point and there are no active transactions in the rollback segment. Typically I always set my INITIAL and NEXT to the same size and have MINEXTENTS set to 4. I then set my OPTIMAL to 4 times the INITIAL size. This seems to work pretty well in my situation but may not be ideal for yours.

Here is the problem if you don't use OPTIMAL. Say you have four rollback segments R01, R02, R03, R04 in the RBS tablespace and all of them have an INITIAL/NEXT of 500K. Your RBS tablespace datafile is 150M. Along the way R02 gets used for a large transaction that makes R02 grow to 249 extents. At that point is is occupying 130M out of 150M, leaving only 20M in the tablespace. The next time around the transaction gets assigned to R03. It starts growing, but when it gets to 60 extents it cannot allocate another extent because there is no more space in the datafile. Also, at this point R01 and R02 cannot grow at all because there is no space left for them to get additional extents. However, if OPTIMAL were set on all of the rollback segments, then they would grow as needed and as soon as the rollback segment isn't needed any more they would shrink back to the OPTIMAL size.

This doesn't solve every possible rollback segment problem, but it sure does solve one that caused many a DBA to be awakened by his beeper in the middle of the night when large batch jobs were executing.

Ken

Huy Vu wrote:

> Hi all,
>
> Today my RBS is full.....I don't know how big the RBS should be....?
>
> Thanks in advance for any idea....
>
> H.V
Received on Wed Jun 23 1999 - 07:16:39 CDT