declare
  cursor c1 is select table_name from table_list;
  ic1 c1%rowtype;

begin

  execute immediate 'alter session enable parallel dml';

  open c1;

   loop

   begin
   fetch c1 into ic1;
   exit when c1%notfound;

    DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask1');

    DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_rowid(
                     task_name => 'mytask1'
                    ,table_owner=>'table_owner'
                    ,table_name=>ic1.table_name
                    ,by_row  => TRUE
                    ,chunk_size  => 20000
                  );

                  

    v_sql := 'insert into tbl_owner.'||ic1.table_name||'@db_link
              select  * from  table_owner.'||ic1.table_name||'
              where rowid between :start_id and :end_id
              ';

 

   DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask1', v_sql, DBMS_SQL.NATIVE,parallel_level => 16);

   DBMS_PARALLEL_EXECUTE.drop_task ('mytask1'); 

   end loop; 

 --commit;              

end;

for rec in (select table_name from table_list) loop
  -- use rec.table_name
end loop;

/*
The source database is 11g.
The target database is 19c.

All codes were executed on an 11g database. 
*/

alter session enable parallel dml

 

create table sch_11g.TRNS_TEST_TABLE nologging
as
select * from all_tables;

 

 

begin
    for r in 1..15 loop
    insert /*+APPEND*/ into sch_11g.TRNS_TEST_TABLE select * from  sch_11g.TRNS_TEST_TABLE;
    end loop;
    commit;

    DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCH_11G', tabname => 'TRNS_TEST_TABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, granularity => 'ALL', degree=>8);
end;

 
declare
 v_sql          varchar2(20000);
begin

   DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask1');
   DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_rowid(
                     task_name => 'mytask1'
                    ,table_owner=>'SCH_11G'
                    ,table_name=>'TRNS_TEST_TABLE'
                    ,by_row  => FALSE
                    ,chunk_size  => 1000
                  );

   v_sql := 'insert  into SCH_19C.TRNS_TEST_TABLE@db_link1
                         (OWNER, TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, IOT_NAME, STATUS, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS,
                          INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BACKED_UP, NUM_ROWS,
                          BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS, DEGREE, INSTANCES,
                          CACHE, TABLE_LOCK, SAMPLE_SIZE, LAST_ANALYZED, PARTITIONED, IOT_TYPE, TEMPORARY, SECONDARY, NESTED, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE,
                          ROW_MOVEMENT, GLOBAL_STATS, USER_STATS, DURATION, SKIP_CORRUPT, MONITORING, CLUSTER_OWNER, DEPENDENCIES, COMPRESSION, COMPRESS_FOR, DROPPED,
                          READ_ONLY, SEGMENT_CREATED, RESULT_CACHE)
                  select  OWNER, TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, IOT_NAME, STATUS, PCT_FREE, PCT_USED, INI_TRANS, MAX_TRANS,
                          INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE, FREELISTS, FREELIST_GROUPS, LOGGING, BACKED_UP, NUM_ROWS,
                          BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS, DEGREE, INSTANCES,
                          CACHE, TABLE_LOCK, SAMPLE_SIZE, LAST_ANALYZED, PARTITIONED, IOT_TYPE, TEMPORARY, SECONDARY, NESTED, BUFFER_POOL, FLASH_CACHE, CELL_FLASH_CACHE,
                          ROW_MOVEMENT, GLOBAL_STATS, USER_STATS, DURATION, SKIP_CORRUPT, MONITORING, CLUSTER_OWNER, DEPENDENCIES, COMPRESSION, COMPRESS_FOR, DROPPED,
                          READ_ONLY, SEGMENT_CREATED, RESULT_CACHE
                  from  SCH_11G.TRNS_TEST_TABLE
                  where rowid between :start_id and :end_id
                  ';

       DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask1', v_sql, DBMS_SQL.NATIVE,parallel_level => 32);

   --DBMS_PARALLEL_EXECUTE.drop_task ('mytask1'); 
end;


select * from USER_PARALLEL_EXECUTE_TASKS

TASK_NAME   CHUNK_TYPE     STATUS
mytask1     ROWID_RANGE    FINISHED

 
--Results :

select count(1) from  sch_11g.TRNS_TEST_TABLE  --130198960

select count(1) from  sch_19c.TRNS_TEST_TABLE@db_link1  --130200150

select count(1) from  sch_11g.trns_test_table  -- 176.551.767

select count(1) from  sch_19c.trns_test_table@db_link1  -- 176.553.227

select t.rowid, count(*)
from   user_parallel_execute_chunks
join   sch_11g.trns_test_table t
on     t.rowid between start_rowid and end_rowid
group  by t.rowid
having count(*) > 1;   -- No rows returned.

 
create table sch_11g.result as 
select * from sch_19c.trns_test_table@db_link1
minus
select * from sch_11g.trns_test_table


select first_column from sch_11g.result  
/* 1460 rows returned. I can't retrieve all the columns. Because it's throwing an error. "ora-01455 converting column overflows integer datatype while export" */

first_column
--------------
"xz­xy
5!xs
*xo17xs
xu[1]
-7xt

2xm
4wÆ              ‑xm,(xi
*/xn9
xg[1]
xw
xx7x{
‑xx
",xz
&xz
[1]9x{[1]
*4xj
‑xj
­xw
)x{[1

xj­‑xl
xp
ÿÿ
ÿ
8000007000440