Re: Flashback query and ORA-01555, rollback segs

DA Morgan wrote:
> Ben wrote:
> > 9.2.0.5 EntEd AIX5L
> >
> > We are using rollback segments due to the fact that our compatible
> > parameter is still set to 8.1.0
> >
> > On to the problem.
> > A user calls and ooops, they've updated 2000 records that shouldn't
> > have been updated. I get a call wanting me to recover/restore that
> > table from last night's backup from tape. I don't want to so I tell
> > them try to use a flashback query.
> >
> > I ran the query and it returns the first portion 50% of the rows but
> > then gives me an ORA-01555: snapshot too old: rollback segment number
> > 121 with name "RBS204" too small.
> > Is that due to the fact that her update or another transaction aged out
> > the portion of the data that is giving this, or is due to the RBS204
> > rollback seg not being large enough to give me all the data that is
> > being returned?
> >
> > Is there something I can do to get it back, short of restore from tape?
>
> It could be either. But the solution is straight forward.
>
> 1. Patch to 8.1.7.4
> 2. Enlarge those rollback segments
>
> You might want to then work on management to move to 10g before your
> resume petrifies. <g>
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

We are actually on 9.2.0.5 with a compatible parameter set to 8.1.0 but point taken.

So but if I enlarge that rollback seg, will it then allow the flashback query to work? The rows in question shouldn't require 200M of rollback and that is what the segment has as optimal. Received on Thu Dec 07 2006 - 12:00:47 CST