CREATE TABLE MAIN_NAME_SPACE.TEMP AS SELECT ID FROM MAIN_NAME_SPACE.MAIN_TABLE

PROCEDURE ARCH AS
  table variable;
BEGIN

  SELECT ID INTO table FROM TEMP;

  FORALL i IN table
    DELETE ARCH_NAME_SPACE.TABLE_1 T WHERE T.ID = i;

  FORALL i IN table
    INSERT INTO ARCH_NAME_SPACE.TABLE_1 VALUES(SELECT * FROM MAIN_NAME_SPACE.TABLE_1 T WHERE T.ID = i);

...

  FORALL i IN table
    DELETE ARCH_NAME_SPACE.TABLE_N T WHERE T.ID = i;

  FORALL i IN table
    INSERT INTO ARCH_NAME_SPACE.TABLE_N VALUES(SELECT * FROM MAIN_NAME_SPACE.TABLE_N T WHERE T.ID = i);

END ARCH;

PROCEDURE DELETE AS
  table variable;
BEGIN

  SELECT ID INTO table FROM TEMP;

  FORALL i IN table
    DELETE MAIN_NAME_SPACE.TABLE_1 T WHERE T.ID = i;

...

  FORALL i IN table
    DELETE MAIN_NAME_SPACE.TABLE_N T WHERE T.ID = i;

END ARCH;

  PROCEDURE DELETE_PROC AS
    minID NUMBER := 1;
    maxID NUMBER := 50000;
    step NUMBER := 50000;
    cnt NUMBER;

    str VARCHAR2(2048);

    TYPE doc_indexes IS TABLE OF VARCHAR2(1024) INDEX BY BINARY_INTEGER;
    index_tbl doc_indexes;
  BEGIN

    -- кол-во записей
    str := 'SELECT COUNT(*) FROM VERY_TEMP_TABLE VTT';
    EXECUTE IMMEDIATE str INTO cnt;
    
    -- считываем индексы
    str := 'SELECT UI.INDEX_NAME FROM USER_INDEXES UI WHERE ui.table_name IN (''TABLE_1'', ''TABLE_2'', ...)
AND UI.INDEX_NAME NOT LIKE ''SYS_%''';
    EXECUTE IMMEDIATE str BULK COLLECT INTO index_tbl;

    str := 'BEGIN ';

    -- выключаем индксы
    FOR i IN 1..index_tbl.COUNT
    LOOP

      -- отключаем индексы
      str := str || 'EXECUTE IMMEDIATE ''ALTER INDEX ' || index_tbl(i) || ' INVISIBLE''; ';

    END LOOP;

    str := str || ' END;';
    EXECUTE IMMEDIATE str;

    WHILE (maxID < cnt)
    LOOP

      -- удаляем
      str := 'BEGIN EXECUTE IMMEDIATE ''alter session enable parallel dml''; ';
      str := str || ' DELETE /*+ parallel(T,12) */ TABLE_1 T WHERE T.PRIMARY_FIELD IN
(SELECT TT.ID FROM TEMP_TABLE TT WHERE ROWNUM BETWEEN ' || minID || ' AND ' || maxID || ');
DELETE /*+ parallel(T,12) */ TABLE_2 T WHERE T.PRIMARY_FIELD IN (); ... END;';
      EXECUTE IMMEDIATE str;

      minID := minID + step;
      maxID := maxID + step;

    END LOOP;
    
    str := 'BEGIN ';

    -- включаем индексы
    FOR i IN 1..index_tbl.COUNT
    LOOP

      str := str || 'EXECUTE IMMEDIATE ''ALTER INDEX ' || index_tbl(i) || ' VISIBLE''; ';

    END LOOP;

    str := str || ' END;';
    EXECUTE IMMEDIATE str;
  
  END DELETE_PROC;