Re: Solution for 01555

<ctcgag_at_hotmail.com> wrote in message news:20030129094658.616
> > Fair enough, on most counts. But for how long does Vlad feel that Oracle
> > should retain the old data on transactions committed after his query has
> > started?
> > I fear we are back to square one..."until all queries needing these data
> > have finished".
>
> I don't wish to speak for Vlad, but I think it would be until all queries
> (in read-committed mode) and/or transactions (in serializable or read-only
> mode) starting before the commit are done. Obviously this means that
> Oracle needs to keep track of the start time of all running
> queries/transactions, so we are now back at square two.
>
> If I were an Oracle developer (or I guess an Oracle competitor) who
> wanted to make Vlad semi-happy, I think I'd go about it by adding
> another mode, "SET LONG RUNNING TRANSACTION ISOLATION LEVEL ....", then
> requiring that rollback data newer than the oldest LONG RUNNING
> transaction cannot be overwritten (except in dire straights,
> i.e. cannot extend error is emminent.)

I really don't get what all the fuss is about. Oracle simply can't record all the necessary information without serious performance issues. Plus, anything it does would be at best a guess. And new features such as flashback would anyway completely screw any such algorithm as you're (and this thread is) suggesting.

But there's been a cure for 1555s for ages available for 7, 8 and 8i, never mind undo_retention in 9i. It's under your control (or the developer's anyway), so Oracle's not left to guess and get it wrong. The performance issues are negligible, because Oracle is not asked to track or remember anything. And the scripts involved are readily available from www.ixora.com.au.

Regards
HJR Received on Wed Jan 29 2003 - 13:28:12 CST