Re: Flashback query and ORA-01555, rollback segs

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