Re: snapshot too old

From: Mark Bole <makbo_at_pacbell.net>
Date: Sat, 28 Feb 2004 01:29:34 GMT
Message-ID: <2aS%b.18079$Q16.15488@newssvr29.news.prodigy.com>

Daniel Morgan wrote:
> Shawn wrote:
>

>> Hi,
>>
>> I've been struggling to update this test table that has about 1.5 million
>> rows in it.  I'm getting this error message:
>>
>> 01555, 00000, "snapshot too old: rollback segment number %s with name 
>> \"%s\"
>> too small"

[...]

>> -- 
>> declare
>>      counter number := 0;
>>      cursor c1 is select ssn from test_table;
>>

[...]
>
> Drop the counter and get the incremental commit out of it that is 90%
> of the problem. One commit after END LOOP is all you should have. If the
> other 10% still causes this to happen your rollback segments are too
> small for your transaction ... make them ALL larger. Do not use SET
> TRANSACTION.
>

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.

 From the documentation, I believe that PL/SQL has a "table" datatype that does the same thing ... in fact in Oracle 9.2 the hash table (indexed by unique strings rather than just numbers) is supported if I read the docs correctly, making this even more feasible.

Is there some reason that all the unique values of the outer loop can't just be read into memory up front? I guess there might be some transactional inconsistency if detail records are being modified after the list is generated, but this seems the kind of thing you'd run during an off hour, and it would be idempotent, anyway.

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 Received on Fri Feb 27 2004 - 19:29:34 CST