Re: ORA-1555 snapshot too old: why?

DA Morgan wrote:
> Chuck wrote:
> > hpuxrac wrote:
> >> Chuck wrote:
> >>> Oracle 9.2.0.7
> >>>
> >>> I got an ORA-1555 snapshot too old today on a export where consistent=y
> >>> was set. The export failed at 6 hrs even though undo_retention was set
> >>> to 8 hours. Querying v$undostat shows that during the time when the
> >>> export was running a total of only 150m of undo was written. The undo
> >>> tablespace is 4g. Why would I get an ora-1555 when undo_retention is set
> >>> plenty high enough, and the undoblks written weren't anywhere near
> >>> maxing out the undo tablespace?
> >>>
> >>> TIA
> >> Tom Kyte has this all covered. Try looking for 1555 in
> >> http://asktom.oracle.com
> >>
> >
> > I've already been there and searched for 1555. I didn't find anything
> > related to ora-1555 that matches this case. All the responses related to
> > manual rbs management, cases where undo_retention was insufficient, or
> > cases where the undo tablespace size was insufficient. My case does not
> > fit any of these.
>
> I agree. I followed HPUXRAC's links too thinking there was something I
> had missed and found nothing like what you are reporting.
>
> I couldn't find an example on metalink so I'd suggest opening an SR.
> I'll be interested in what you discover.

They would likely find Note:174782.1, tell him someone else was updating an object so Oracle wouldn't be able to make a read-consistent view of the data for exp, and to use consistent=n as a workaround.

Remember Chuck, the key to most ORA-155x errors is to think of what else is happening to the data. It is easy to think "well, no one else is on" when oracle still hasn't cleaned up things, or indeed, people shut off their client pc's and go home. Of course, if you are using consistent=y, that means you already know there will be other people updating after the exp starts - have you considered those that have started updating before the exp starts? George updates a row in big_transaction_table at 4PM, bunch of others update much stuff from 4PM to 1AM overwriting George's undo, export starts at 11PM, needs to construct view of big_transaction_table at 5AM, barfs. By the time you look at v$undostat at 5:10AM, it's all gone. Did the ORA- leave a query in the alert log? Do you have big-George batch jobs?

Personally I like to kill off any attaches to the db before exporting without setting consistent, but that's reasonable in my environment. YMMV. jg

-- 
@home.com is bogus.
http://catless.ncl.ac.uk/Risks/24.51.html#subj1