Re: Oracle ORA-1555 Snapshot Too Old error
Date: 1996/10/24
Message-ID: <326FCEFE.39F1_at_boekhuis.nl>#1/1
E. Fong wrote:
>
> Anyone,
>
> I having a problems getting an ORA-1555 Snapshot too old error while trying
> to load a large set of data into a table. The batch job is run a not
> concurrently with other nightly batch jobs and generally runs an hour long.
> It seems that the rollback segments have been aged out by the end of the
> run. I have tried using a larger rollback segment but hasn't helped.
>
> Does anyone have any insight to a solution for this problem?
>
> Wondering,
>
> E. FongEven this solution didn't solve MY Snapshot too old problem. You were right about the
log-files, it a ring buffer with a 'head' and 'tail'. But since your cursor keeps being
opened, it 'holds' the tail of the ring and eventually the head catches up again and you
get the same annoying message. It has to hold the tail because you use a cursor and
oracle wouldn't want the result of the cursor to change while fetching rows. So 'fetch
across commits' is not recommended to solve the 'Snapshot too old' problem.
A solution I use is to close and open the cursor after every commit. However, you would have to use a parameter to the cursor in order to have it opened for the NEXT set and not the same set you've just fetched.
A solution might be:
declare
cursor big_one(some_id number) is
select xyz
from abc
where abc.some_id > some_id
and rownum < 2000;
l_id number := 0;
begin
open big_one(l_id);
fetch big_one into pqr;
while big_one%found loop
process(pqr) /* do your thing */
fetch big_one into pqr;
if big_one%notfound then
close big_one;
commit;
l_id := l_id + 2000;
open big_one(l_id);
fetch big_one into pqr
end if;
end loop;
close big_one;
commit;
end; Received on Thu Oct 24 1996 - 00:00:00 CEST