Copy Large table
create table originalTable
(column1 number,
column2 number
);
begin
for i in 1..100
loop
insert into originalTable values ( i,i*100);
end loop;
commit;
end;
/
create table copyTable as select * from originalTable where 1=2;
Create or replace Procedure CopyLargeTable (batchNumber number) is
cursor tableCur is select * from originalTable where mod(column1,10)=batchNumber;
type tableCurtype is table of tableCur%rowtype;
tableArray tableCurtype;
begin
open tableCur;
loop
fetch tableCur bulk collect into tableArray limit 10000;
exit when tableArray.count=0;
forall indx in 1..tableArray.count
insert into copyTable values (
tableArray(indx).column1,
tableArray(indx).column2
);
commit;
end loop;
end CopyLargeTable;
------------------
-- # UnitTestCode
------------------
select * from copyTable
begin
CopyLargeTable (0);
end;
/
begin
execute immediate 'truncate table copyTable';
dbms_scheduler.create_job(job_name => 'copyLargeTable0',
job_type => 'PLSQL_BLOCK',
job_action => ' begin CopyLargeTable (0); end;',
start_date => sysdate,
enabled => true);
dbms_scheduler.create_job(job_name => 'copyLargeTable1',
job_type => 'PLSQL_BLOCK',
job_action => ' begin CopyLargeTable (1); end;',
start_date => sysdate,
enabled => true);
dbms_scheduler.create_job(job_name => 'copyLargeTable2',
job_type => 'PLSQL_BLOCK',
job_action => ' begin CopyLargeTable (2); end;',
start_date => sysdate,
enabled => true);
dbms_scheduler.create_job(job_name => 'copyLargeTable3',
job_type => 'PLSQL_BLOCK',
job_action => ' begin CopyLargeTable (3); end;',
start_date => sysdate,
enabled => true);
dbms_scheduler.create_job(job_name => 'copyLargeTable4',
job_type => 'PLSQL_BLOCK',
job_action => ' begin CopyLargeTable (4); end;',
start_date => sysdate,
enabled => true);
dbms_scheduler.create_job(job_name => 'copyLargeTable5',
job_type => 'PLSQL_BLOCK',
job_action => ' begin CopyLargeTable (5); end;',
start_date => sysdate,
enabled => true);
dbms_scheduler.create_job(job_name => 'copyLargeTable6',
job_type => 'PLSQL_BLOCK',
job_action => ' begin CopyLargeTable (6); end;',
start_date => sysdate,
enabled => true);
dbms_scheduler.create_job(job_name => 'copyLargeTable7',
job_type => 'PLSQL_BLOCK',
job_action => ' begin CopyLargeTable (7); end;',
start_date => sysdate,
enabled => true);
dbms_scheduler.create_job(job_name => 'copyLargeTable8',
job_type => 'PLSQL_BLOCK',
job_action => ' begin CopyLargeTable (8); end;',
start_date => sysdate,
enabled => true);
dbms_scheduler.create_job(job_name => 'copyLargeTable9',
job_type => 'PLSQL_BLOCK',
job_action => ' begin CopyLargeTable (9); end;',
start_date => sysdate,
enabled => true);
end;
/
select * from user_scheduler_jobs where job_name like '%COPYLARGETABLE%'
select * from user_scheduler_job_log where job_name like '%COPYLARGETABLE%'
