Re: snapshot too old

Mark Bole wrote:

> Here's what I'm curious about, on the technical side.
>
> As one who programs in Perl more often than I do in PL/SQL, it is second
> nature to grab all key (ID) values from a table in memory as an array
> (list), and then loop on the in-memory array to perform the specific
> actions for each record. AFAIK, this completely avoids the ORA-1555
> error, no matter how many times you do or don't commit within the loop.

Actually it doesn't. You are as capable of getting an ORA-01555 as everyone else. What causes most ORA-01555's is Oracle trying to find something in a rollback or undo segment that has been overwritten.

You may not be getting them ... but your architecture is not the reason. I haven't seen one in years either and don't do what you do.

> Is there some reason that all the unique values of the outer loop can't
> just be read into memory up front?

No.

  I guess there might be some
> transactional inconsistency if detail records are being modified after
> the list is generated,

Whoa. You have an apparent gap in understanding on the most basic architecture of Oracle and how it works. Almost no matter how you handle things in Oracle changes made after you begin your transaction are irrelevant: Get a copy of Tom Kyte's book "Expert one-on-one Oracle" and read the first three chapters.

  but this seems the kind of thing you'd run during
> an off hour, and it would be idempotent, anyway.

You are correct about when it might be run but when it is run is irrelevant to the problem.

> When the UNDO tablespace becomes 10-20% of your total disk usage, it is
> a problem you have to be on top of.
>
> --Mark Bole

Why?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)