Re: ORA-01555 and select count(*) trick?

Hi:

I prefer to use a very large rollback segment and do not commit within the loop. This can also improve performance.

Regards.

Michael.

<psalmu_at_my-deja.com> wrote in message news:7mlrqt$v8v$1_at_nnrp1.deja.com...
> I'm getting ORA-01555 in a long running PL/SQL program and it
> appears to be because of something called "delayed block cleanout"
> which I'm not even interested to understand. A possible workaround
> mentioned is to do a full table scan, e.g. select count(*) to some
> table somewhere, but what is the actual _recipe_ for that? Is it like
> this:
>
> CURSOR curs IS
> SELECT * FROM reallybigtable;
> BEGIN
> FOR curs_data IN curs LOOP
> ...
> INSERT INTO smalltable...
> ...
> IF <every thousandth row or so> THEN
> COMMIT;
> SELECT COUNT(*) INTO dummy FROM reallybigtable; -- Add this?
> END IF;
> END LOOP;
>
> Or something else, what?
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Fri Jul 16 1999 - 04:45:23 CEST