Re: Alert to trap/anticipate ORA-01555 before its occurence

Spendius wrote:
> Hi,
> Has someone perhaps thought of means to prevent this error ?
> What SQL, against which views...
> Wonder if I could add an OEM event related to this error.
>
> Thanks.
> Regards,
> Spendius

First, you could upgrade to 9i, implement undo_retention, provide enough disk space for the retention to be feasible, and then never have to worry about 1555s again, mostly.

Second, in any version earlier than 9i, you can completely eliminate the possibility of 1555s by first ensuring there are no pending transactions, and then raising a dummy transaction in each and every rollback segment before running your long-running report. A dummy transaction prevents anything "ahead" of it being over-written. A report that is then run will only need rollback generated after the time it starts to produce a read-consistent image of the data -which rollback must definitely be safe and available, because of the dummy transaction.

Only trouble is, you need one dummy transaction raised for (and for it to have its rollback placed in) each and every rollback segment you have -because you don't know which rollback segment will contain the rollback needed for a particular read-consistent image, so you'd better make sure that rollback is safe in all of them, just in case.

Incidentally, scripts to achieve something very like this, only with a bit more subtlety and sophistication, can be downloaded from Steve Adams' website at www.ixora.com.au.

I think the upgrade to 9i and the setting of UNDO_RETENTION might be the easier approach!

Regards
HJR Received on Mon Dec 13 2004 - 13:03:04 CST