title: ORA-01555: snapshot too old error when deleting with transactions
body: <p>I hope someone can explain me why I'm seeing ORA-01555. I have a function and a procedure to perform a cleanup in a huge table:</p>
  <pre><code>-- Small cleanup in separate transaction.
  FUNCTION clean_single(ts_until IN TIMESTAMP, datapoint_id IN NUMBER) RETURN NUMBER IS
      PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
      DELETE FROM VALUES WHERE DATAPOINT_ID = datapoint_id AND TS &lt; ts_until;
      COMMIT;
      RETURN sql%rowcount;
  END clean_single;
  
  PROCEDURE prc_clean IS
      count_all_deleted_vals NUMBER(10) := 0;
  BEGIN
      BEGIN
          FOR dps IN (
              SELECT x AS dpid, y as tsUntil FROM Z where some conditions
              LOOP
                  count_all_deleted_vals := count_all_deleted_vals + clean_single(dps.tsUntil, dps.dpid);
              END LOOP;
          DBMS_OUTPUT.PUT_LINE('Removed ' || count_all_deleted_vals || ' values');
      END;
  END prc_clean;
  </code></pre>
  <p>The idea is to run <code>prc_clean()</code> from a job and after the relevant datapoint ids are selected, the deletion per datapoint-id is done in a single transaction to avoid having one huge transaction.</p>
  <p>But when I run this it runs for a while and then fails with ORA-01555.
  In detail i do not understand why this is happening. Why does the <code>PRAGMA AUTONOMOUS_TRANSACTION;</code> in the function not prevent this?
  What can I do to prevent it?</p>