Need help to create purging operation executing in loop

Question

I am going to run one pl/sql block which deletes all the data which is older than 30 days in a table.

Condition is like this:

It will delete 200k data at a time and after deleting 200k data I have to give wait period for 10-15 sec. There are around 100 million records in that table. The whole deletion process I want to do it in automation script.

DECLARE
   CURSOR c6
   IS
      SELECT                                               /*+parallel(a,32)*/
            a.rowid,a.*
        FROM EB_O.CCO_DIR_CONTRS_ES_GG a
       WHERE a.GG_CREATE_DATE < SYSDATE-30
       AND ROWNUM <=200001;

   TYPE contact_point_id_tab IS TABLE OF c6%ROWTYPE
                                   INDEX BY PLS_INTEGER;

   l_contact_point_id_tab   contact_point_id_tab;
BEGIN
   OPEN c6;

   LOOP
      FETCH c6
      BULK COLLECT INTO l_contact_point_id_tab
      LIMIT 10000;

      EXIT WHEN l_contact_point_id_tab.COUNT = 0;

      IF l_contact_point_id_tab.COUNT > 0
      THEN
         FORALL i
             IN l_contact_point_id_tab.FIRST .. l_contact_point_id_tab.LAST
            DELETE FROM  EB_O.CCO_DIR_CONTRS_ES_GG
                            WHERE rowid =l_contact_point_id_tab (i).rowid;

         COMMIT;
      END IF;

      l_contact_point_id_tab.delete;
   END LOOP;
END;

This is the above plsql block I have written. How to do this operation in multiple loop and after every loop there will be wait period of 10-15 sec and again the deletion operation will happen for next 200k data. the loop will continue until all the data will be deleted.

N.B.: for wait period, grant is no there for DBMS_LOCK.sleep

Possible duplicate of [Sleep function in ORACLE](https://stackoverflow.com/questions/2561671/sleep-function-in-oracle) — XING, Apr 26 '19 at 06:12
seems the same question: https://stackoverflow.com/questions/5757268/how-to-fetch-delete-commit-from-cursor — Ravshan Abdulaev, Apr 26 '19 at 08:49

score 0 · Answer 1 · answered Apr 26 '19 at 08:06

I would not do this by looping over a cursor. Instead, I would do something like:

begin
  loop
    delete FROM EB_O.CCO_DIR_CONTRS_ES_GG
    WHERE GG_CREATE_DATE < SYSDATE-30
    AND ROWNUM <=200000;

    exit when sql%rowcount = 0;

    commit;

    dbms_lock.sleep(10);
  end loop;
end;
/

This way, you avoid the potential snapshot-too-old error (because you have committed across a cursor fetch loop), and you're no longer fetching information into memory to pass back to the database, which makes things more efficient.

score 0 · Answer 2 · answered Jun 27 '19 at 12:02

If you don't have access to sleep function, just create your own. Store the output of below query in a variable, create a loop which exits when sysdate is equal to the value stored in your variable.

select sysdate+(10 / (24*60*60)) from dual;

Also, instead of hardcoding 10 you can take the seconds as input variable and pass the same in the query.

Need help to create purging operation executing in loop

2 Answers2