Re: rollback segment with no optimal question

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sun, 23 Nov 2003 06:22:00 +1100
Message-ID: <3fbfb764$0$13498$afc38c87@news.optusnet.com.au>

"anu" <anu_d_77_at_yahoo.com> wrote in message news:ba155ca5.0311221011.3a55fb28_at_posting.google.com...
> Hi,
> I have rollback segment with no optimal set. the rollback tablespace
> has a lot of space. It is a OLTP system with many but small
> transactions. So I expect no shrinking. However for one report that
> runs around 20 minutes we are getting ora-01555 snapshot too old. the
> intial and next are set to 1M which I think is OK for OLTP transaction
> size. There are many rollback segments = 40. So how do I go about
> increasing the size or the number and on what basis? Thank you

Well, to increase the number, it's just a question of creating more of them, and making sure they are online and are brought online at each startup:

create rollback segment X tablespace Y;
alter rollback segment X online;

and in the init.ora, change

ROLLBACK_SEGMENTS=A,B,C to

ROLLBACK_SEGMENTS=A,B,C,X There is no way to make an existing rollback segment bigger really. Best thing would be to drop them and re-create them:

alter rollback segment A offline;
drop rollback segment A;
create rollback segment A storage (initial 5M next 5M minextents 20);

Or whatever values are appropriate, of course. And you have to do it one by one.

A variation on this theme stems from the fact that I dislike seeing storage clauses specified at the segment level. The default storage clause of the tablespace itself should be doing the job. Therefore:

Edit init.ora so that

ROLLBACK_SEGMENTS=A,B,C is changed to

#ROLLBACK_SEGMENTS=A,B,C

2. Shutdown immediate
3. Startup restrict
4. Drop all rollback segments
5. Drop the rollback segment tablespace
6. Remove the O/S file from disk
7. Re-create the rollback segment tablespace with a better default storage

clause
8. Re-create all rollback segments with the simple command 'create rollback segment A tablespace Y' (ie, no storage clause) 9. Shutdown immediate
10. Edit the init.ora, and re-instate the ROLLBACK_SEGMENTS line 11. Startup.

Regards
HJR Received on Sat Nov 22 2003 - 13:22:00 CST