I'm trying to create a copy of a very large table and working to achieve this using FORALL and scheduler jobs. But I'm missing something in my script which is causing to miss records after the job is completed (record count in target table doesn't match with the source table). I'm able to replicate this using below example. Can someone please point me what I'm missing or doing incorrectly?
CREATE TABLE test_nolog_src (emp_key NUMBER, dept_num NUMBER, last_name VARCHAR2(100), first_name VARCHAR2(100), product_info VARCHAR2(100));
CREATE TABLE test_nolog_tgt NOLOGGING AS SELECT * FROM test_nolog_src WHERE 1 = 2;
DECLARE
v_dept_num NUMBER;
BEGIN
FOR I IN 1 .. 3635 LOOP
IF I < 1200 THEN
v_dept_num := 1;
ELSIF I BETWEEN 1201 AND 3000 THEN
v_dept_num := 2;
ELSE
v_dept_num := 3;
END IF;
INSERT INTO test_nolog_src (emp_key, dept_num, last_name, first_name, product_info)
VALUES (I, v_dept_num, 'last '||I, 'first '||I, 'product details '||I);
END LOOP;
END;
/
CREATE OR REPLACE PACKAGE test_log_pkg AS
PROCEDURE test_log_insert1;
PROCEDURE test_log_insert2;
END;
/
CREATE OR REPLACE PACKAGE BODY test_log_pkg AS
PROCEDURE test_log_insert1 AS
TYPE test_array_type IS TABLE OF test_nolog_src%rowtype INDEX BY BINARY_INTEGER;
test_array_object test_array_type;
fetch_size NUMBER := 50;
CURSOR test_cursor IS
SELECT * FROM test_nolog_src WHERE emp_key < 1818;
BEGIN
OPEN test_cursor;
LOOP
FETCH test_cursor BULK COLLECT
INTO test_array_object LIMIT fetch_size;
EXIT WHEN test_cursor%notfound;
FORALL I IN INDICES OF test_array_object SAVE EXCEPTIONS
INSERT/*+ APPEND */ INTO test_nolog_tgt VALUES test_array_object(I);
COMMIT;
END LOOP;
CLOSE test_cursor;
END test_log_insert1;
PROCEDURE test_log_insert2 AS
TYPE test_array_type IS TABLE OF test_nolog_src%rowtype INDEX BY BINARY_INTEGER;
test_array_object test_array_type;
fetch_size NUMBER := 50;
CURSOR test_cursor IS
SELECT * FROM test_nolog_src WHERE emp_key > 1817;
BEGIN
OPEN test_cursor;
LOOP
FETCH test_cursor BULK COLLECT
INTO test_array_object LIMIT fetch_size;
EXIT WHEN test_cursor%notfound;
FORALL I IN INDICES OF test_array_object SAVE EXCEPTIONS
INSERT/*+ APPEND */ INTO test_nolog_tgt VALUES test_array_object(I);
COMMIT;
END LOOP;
CLOSE test_cursor;
END test_log_insert2;
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'job_testnolog_insert1',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN test_log_pkg.test_log_insert1; END;',
start_date => SYSTIMESTAMP,
enabled => TRUE
);
DBMS_SCHEDULER.create_job (
job_name => 'job_testnolog_insert2',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN test_log_pkg.test_log_insert2; END;',
start_date => SYSTIMESTAMP,
enabled => TRUE
);
END;
/
Source table records:
SELECT COUNT(1) FROM test_nolog_src;
COUNT(1)
----------
3635
Target table records:
SELECT COUNT(1) FROM test_nolog_tgt;
COUNT(1)
----------
3600
Using:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Windows 11 Enterprise