Re: Flashback query and ORA-01555, rollback segs

Ben wrote:
> 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.

Using optimal is just plainly *EVIL* and the *VERY CAUSE* of your ora-1555.
Disable optimal and start living.

-- 
Sybrand Bakker
Senior Oracle DBA