>
>
> I encountered the problem of rollback segments being too small a while ago
> - and increasing them fixed it for a period of time. I just had to add
> another 300M. But what I am noticing, is that the used space in the
> rollback segments never decreases from the size they were when I added the
> new tablespace.
>
> TS FILE_ID BYTES USED FREE
> PCT_USED
> ------------------------- ---------- ---------- ---------- ----------
> ----------
> RBS 2 104857600 68161536 36696064
> 65.0039063
> RBS 10 52428800 51122176 1306624
> 97.5078125
> RBS 14 157286400 46862336 110424064
> 29.7942708
> RBS 15 314572800 4096 314568704
> .001302083
>
>
> Even when reboot the servers and restart oracle, they do not decrease in
> percent used. (I noticed this last time when the third one was added - the
> other two never dropped below 22% and 16%).
>
> Is there anything I can run that will clean them up?
>
> -Christine
Your condition is common. Rollback segments to not shrink when a
transaction is complete. You need to set your OPTIMAL size of each
rollback segment. Even then, it does not always shrink. You have to
attempt to extend the rollback segment past the current extent for it
to shrink to its OPTIMAL size. Otherwise, you can issue "ALTER ROLLBACK
SEGMENT xxx SHRINK;" This will cause the rollback segment to shrink to
the optimal size...do this for each rollback segment, and your USED space
will decrease.
Best of luck,
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 160+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
>
> _______________________________________
> Christine Hales, System Analyst
> AEA Management
> Phone (519) 254-4410 Ext. 146
> Fax (519) 258-6884
>
> ----------
> > From: akaplan@yang.interaccess.com
> > To: Christine Hales
> > Subject: Re: Rollback Segments Too Small
> > Date: Wednesday, January 28, 1998 12:35 PM
> >
> > Christine -
> >
> > You do not need to shut down the database to do this. Although I do not
> > know your database situation, I would recommend increasing the rollback
> tablespace size. You have 10 segments and 150M of space. This leaves just
> 15M for
> > each rollback segment on average.
> >
> > You can tell by sure by doing:
> >
> > SELECT SUM(BYTES)/1024/1024 Megs
> > FROM DBA_FREE_SPACE
> > WHERE TABLESPACE_NAME = 'RBS';
> >
> > Replace RBS with the name of your rollback tablespace. You will see how
> > much or little space you have left.
> >
> > I'd recommend doubling your space to 300M. Try re-running the program
> > that caused an error. If you still get one, keep increasing the size of
> the rollback segments until the error stops.
> >
> > -Ari
> > >
> > >
> > > In recreating the rollback segments - do I shutdown the database to do
> > > this - or can I do it while the database is up? And do I have to
> increase
> > > the tablespaces as well?
> > >
> > > -Christine
> > > _______________________________________
> > > Christine Hales, System Analyst
> > > AEA Management
> > > Phone (519) 254-4410 Ext. 146
> > > Fax (519) 258-6884
> > >
> > > ----------
> > > > From: akaplan@interaccess.com
> > > > To: chales@schukra.com
> > > > Subject: Re: Rollback Segments Too Small
> > > > Date: Monday, January 12, 1998 12:25 PM
> > > >
> > > > >
> > > > > I got the following error:
> > > > >
> > > > > ORA-01555: snapshot too old (rollback segment too small) : errno
> > > -1555:
> > > > >
> > > > > I have 10 rollback segments, each identical in design
> > > > > (initail 128K, next 128K, optimal 256K)
> > > > > And I have 2 rbs tablespaces, the first 50M, the next 100M.
> > > > > What do I need to increase?
> > > > >
> > > > > Thanks in advance,
> > > > > Christine
> > > > Christine,
> > > >
> > > > You need to increase either the INITIAL, NEXT, or both. What is
> happening
> > > is
> > > > one process, attached to one rollback segment, took too much data for
> > > > one rollback segment to keep the process's view of the data in a
> > > consistent mode.
> > > >
> > > > So, drop all rollback segments and recreate. For starters, try:
> > > >
> > > > INITIAL 1M
> > > > NEXT 1M
> > > > OPTIMAL 2M
> > > > MINEXTENTS 2
> > > >
> > > >
> > > > Best of luck,
> > > >
> > > >
> > > > -Ari Kaplan
> > > > Independent Oracle DBA Consultant
> > > >
> > > > <-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
> > > > <-> For 150+ Oracle tips, visit my Web Page: <->
> > > > <-> <->
> > > > <-> www.arikaplan.com <->
> > > > <-> <->
> > > > <-> email: akaplan@interaccess.com <->
> > > > <-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
> > >
> >
>
Back to Ari Kaplan's Home Page