Re: HELP: Get ORA-1555 rollback too small

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Tue, 3 Dec 2002 06:08:01 +1100
Message-ID: <F3OG9.88862$g9.249416@newsfeeds.bigpond.com>

"Ban Spam" <ban-spam_at_operamail.com> wrote in message
news:Xns92D8438ADF7DSunnySD_at_68.6.19.6...

> "A. Dischner" <dischner_at_klch.med.uni-muenchen.de> wrote in news:dischner-
> CE4085.15331002122002_at_hydra1.fw.med.uni-muenchen.de:
>
> > Hi,
> >
> > with Oracle 8.1.7.0.1 i get ORA-1555 which means Rollback-segment is
> > too small.
> >
> > I have really big rollback segments: Tablespace 4gb, initial 4MB, next
> > 4MB, optimal 16MB, max 4096, number of RBsegs: 4
> >
> > Tablespace usage is always < 1% as seen in Enterprise manager.
> >
> > There are no critical locks.
> >
> > any ideas? hints?
> >
> > Toni
> >
>
> ORA-01555 can also mean that the code is issuing COMMITs
> too frequently.

Not really. If you had an infinitely large rollback segment, then you could commit every nanosecond and you still wouldn't loop round the segment and start over-writing the earlier rollback.

Of course, given that rollback segments are of finite size, then yes: frequent commits give the segment the go-ahead to overwrite the committed rollback, and thus give the 1555 a chance to occur. And so it is definitely the case that inappropriate commits contribute to the problem. But it still comes down, fundamentally to size. After all: what will happen if we don't commit so frequently? Then the rollback segment won't be permitted to overwrite the earlier rollback, and the segment will begin to grow dynamically in response.

Regards
HJR Received on Mon Dec 02 2002 - 13:08:01 CST