Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Rollback segment question
Tony Adolph wrote:
>
> Hello All,
>
> I am getting
>
> *** 1999.09.08.12.26.34.717
> SMON: following errors trapped and ignored:
> ORA-00604: error occurred at recursive SQL level 1
> ORA-01555: snapshot too old: rollback segment number 33 with name
> "BIG_RBS01" too small
>
> I have 33 rollback segments with an initial and next extent of 102400
> and BIG_RBS01 which has an initial of 150M and next of 1M.
>
> It was my intention for only the reports scripts to use BIG_RBS01 via
> the alter session use rollback.....etc. All of the rollback segs are
> private and there seems to be freespace enough for all of the segments
> to grow.
>
> What have I missed out?
>
> Thanks in advance
> Tony Adolph
>
> PS Ora 7.3.4 on NT4
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Snapshot too old is not running out of rollback - what is happening is that the report is trying to get at some data that has been aged out of the rollback segments (probably by their re-use by other transactions)...
Its the transactions that need the larger rollback area so that the reports can still get to the original data...
For simplicity sake, I normally set all my rollbacks to the same (quite large) size to avoid this problem. Judicious use of OPTIMAL (or autoextend/maxextents) can be used to ensure that these large segments don't grow out of control
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Fri Oct 08 1999 - 06:07:15 CDT