Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Snapshot Too Old Reported - But We Doubt It

Re: Snapshot Too Old Reported - But We Doubt It

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 3 Aug 2004 05:58:35 +1000
Message-ID: <410e9cc0$0$2542$afc38c87@news.optusnet.com.au>

"Alan" <alan_at_erols.com> wrote in message news:2n7icrFu37o7U1_at_uni-berlin.de...

[snip]

> > Or you can visit www.ixora.com.au, search for 1555, and use Steve's
> scripts
> > to raise 90 dummy blocking transactions just prior to your update. That
> will
> > prevent the undo for your transactions cycling over the top of other
undo,
> > and at that point your large setting for MAXEXTENTS will kick in to
result
> > in constantly-growing undo segments. You will end up with a single huge
> > segment, and the extra size will have prevented the 1555 from occurring.
> >
>
> Interesting idea, but SET TRANSACTION seems a lot easier (an safer).

Have a look at Steve's scripts. All they do is use 'set transaction' to place a dummy transaction in every rollback segment and then, after a defined time (say, 2 hours and ten minutes), roll it back. Nothing 'unsafe' in that lot at all, and using exactly the same technology ("set transaction") as you otherwise seem to feel is an OK thing to do.

The only thing I would caution you about embedding 'set transaction' commands in your own code (and hence a vote in favour of using Steve's scripts instead) is that set transaction causes an error in 9i when automatic undo is in use. It's an error that can be ignored by setting a parameter (UNDO_SUPPRESS_ERRORS, if you're interested), but without that parameter being set, your application would appear to have broken as a result of the upgrade. That said, it's not an issue in 10g.

> > Or you can upgrade to 9i, and switch to using automatic undo, and never
> have
> > to worry about the matter again!
>
> Would that I could...<sigh>
>
> Thanks much!!!

No worries.
Regards
HJR Received on Mon Aug 02 2004 - 14:58:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US