ORA-01555 and select count(*) trick?

From: <psalmu_at_my-deja.com>
Date: Thu, 15 Jul 1999 23:45:35 GMT
Message-ID: <7mlrqt$v8v$1@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 Thu Jul 15 1999 - 18:45:35 CDT