Ok, I'll try to explain the whole scenario (although my problem is in the last step). The code is quite long but it is very simple, since it is quite repetitive.
My scenario is the following: I've to backup some tables of my production environment to another database that is on another server. On backup-database side there is a job scheduled to run every night at 2. This job executes a procedure that performs the following operations:
- copy, through dblink, all data I need from production environment
- delete, through dblink, the copied data from production environment
The delete operation is carried out through three different calls to three functions defined in a package on production environment.
BACKUP_DATABASE SIDE:
CREATE OR REPLACE PROCEDURE BACKUP_SCHEMA.BACKUP_DATABASE (BACKUP_DATE IN OUT DATE)
AS
/*DECLARE */
.....
BEGIN
.....
--copy data from DOCUMENTS tables through DBLINK to production environment
copy_documents (STEP, documentid);
.....
--copy data from ACCOUNT tables through DBLINK to production environment
copy_accounts (STEP, REFERENCE, ACC_VERSION, STARTDATE);
.....
--copy data from NOTIFICATION through DBLINK to production environment
copy_notifications (STEP, NOTIFICATIONS);
.....
--delete data from DOCUMENTS tables through DBLINK from production environment
warning_message := TO_CLOB (backup_pkg.delete_documents@DBLINK_TO_PROD);
log_result(warning_message);
--delete data from NOTIFICATIONS tables through DBLINK from production environment
warning_message := TO_CLOB (backup_pkg.delete_notifications@DBLINK_TO_PROD);
log_result(warning_message);
--delete data from ACCOUNT tables through DBLINK from production environment
warning_message := TO_CLOB (backup_pkg.delete_accounts@DBLINK_TO_PROD);
log_result(warning_message);
...
END;
/
My problem is on last delete call (backup_pkg.delete_accounts@DBLINK_TO_PROD), since the previous delete calls are performed on tables with few data (the execution is too fast to check if multiple chunks are assigned or not for these tables, but for medium tables it seems to work (almost) properly).
On production environment side the package is defined as following:
CREATE OR REPLACE PACKAGE PRODUCTION_SCHEMA.backup_pkg
AS
FUNCTION delete_from_table (STEP IN VARCHAR2,
field_name_1_param IN VARCHAR2,
field_name_2_param IN VARCHAR2,
TABLE_NAME_PARAM IN VARCHAR2)
RETURN VARCHAR2;
FUNCTION delete_accounts
RETURN VARCHAR2;
FUNCTION delete_documents
RETURN VARCHAR2;
FUNCTION delete_notifications
RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY PRODUCTION_SCHEMA.backup_pkg
IS
FUNCTION delete_from_table (STEP IN VARCHAR2,
field_name_1_param IN VARCHAR2,
field_name_2_param IN VARCHAR2,
TABLE_NAME_PARAM IN VARCHAR2
)
RETURN VARCHAR2
AS
PRAGMA AUTONOMOUS_TRANSACTION;
check_task_exists CHAR (1);
query_chunk_generator VARCHAR2 (1000);
message_to_return CLOB;
message_string VARCHAR2 (4000) := '';
parallel_task_name VARCHAR2 (100) := 'delete_parallel_task';
attempt_number NUMBER;
task_status NUMBER;
delete_stmt VARCHAR2(500);
retry BOOLEAN;
BEGIN
message_to_return := EMPTY_CLOB ();
.....
IF field_name_2_param IS NULL OR field_name_2_param = ''
THEN
query_chunk_generator := 'SELECT ROWID, ROWID FROM ' || table_name_param || ' WHERE ' || field_name_1_param || ' IN ( select ' || field_name_1_param || ' from ' || table_name_param || '@dblink_to_backup) order by ' || field_name_1_param || ' desc';
ELSE
query_chunk_generator := 'SELECT ROWID, ROWID FROM ' || table_name_param || ' WHERE (' || field_name_1_param || ',' || field_name_2_param || ')IN ( select ' || field_name_1_param || ',' || field_name_2_param || ' from ' || table_name_param || '@dblink_to_backup) order by ' || field_name_1_param || ' desc, ' || field_name_2_param || ' DESC';
END IF;
DBMS_PARALLEL_EXECUTE.CREATE_TASK (parallel_task_name);
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL (parallel_task_name, query_chunk_generator, TRUE);
delete_stmt := 'DELETE FROM ' || table_name_param || ' WHERE ROWID IN (:start_id,:end_id) ';
DBMS_PARALLEL_EXECUTE.RUN_TASK (parallel_task_name,
delete_stmt,
DBMS_SQL.NATIVE,
parallel_level => 10);
-- If there is error, RESUME it for at most 10 times.
attempt_number := 0;
task_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS (parallel_task_name);
retry := task_status = DBMS_PARALLEL_EXECUTE.FINISHED_WITH_ERROR OR task_status = DBMS_PARALLEL_EXECUTE.CRASHED;
WHILE (retry)
LOOP
attempt_number := attempt_number + 1;
DBMS_PARALLEL_EXECUTE.RESUME_TASK (parallel_task_name);
task_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS (parallel_task_name);
retry := (attempt_number < 10 AND (task_status = DBMS_PARALLEL_EXECUTE.FINISHED_WITH_ERROR OR task_status = DBMS_PARALLEL_EXECUTE.CRASHED));
END LOOP;
IF task_status = DBMS_PARALLEL_EXECUTE.FINISHED_WITH_ERROR
THEN
message_to_return := 'ERRORS ON STEP: ' || step || (CHR (13) || CHR (10));
message_to_return := message_to_return || 'TABLE: ' || table_name_param || (CHR (13) || CHR (10));
FOR messages IN (SELECT error_message
FROM user_parallel_execute_chunks
WHERE task_name = parallel_task_name AND status = 'PROCESSED_WITH_ERROR')
LOOP
message_to_return := message_to_return || 'ERROR: '||messages.error_message|| (CHR (13) || CHR (10));
END LOOP;
ELSE
message_to_return := EMPTY_CLOB ();
END IF;
DBMS_PARALLEL_EXECUTE.drop_task (parallel_task_name);
COMMIT;
message_string := DBMS_LOB.SUBSTR (message_to_return, 4000, 1);
RETURN message_string;
EXCEPTION
WHEN OTHERS
THEN
message_to_return := 'ERRORS ON STEP: ' || step || (CHR (13) || CHR (10));
message_to_return := message_to_return || 'DELETING RECORDS FROM TABLE: ' || table_name_param || (CHR (13) || CHR (10));
message_to_return := message_to_return || 'ERROR: '||SQLERRM || (CHR (13) || CHR (10));
message_string := DBMS_LOB.SUBSTR (message_to_return, 4000, 1);
RETURN message_string;
END;
FUNCTION delete_bookings
RETURN VARCHAR2
IS
/* DECLARE */
table_name VARCHAR2 (30);
field_name_1 VARCHAR2 (30);
field_name_2 VARCHAR2 (30);
message_to_return VARCHAR2 (4000);
step VARCHAR2 (50) := 'DELETE BOOKINGS';
BEGIN
message_to_return := EMPTY_CLOB ();
table_name := 'ACC_ACCOUNT';
field_name_1 := 'accountreference';
field_name_2 := 'accountversion';
message_to_return :=
delete_from_table (step,
field_name_1,
field_name_2,
TABLE_NAME
);
IF LENGTH (message_to_return) > 0
THEN
RETURN message_to_return;
END IF;
table_name := 'ACC_ACCOUNTCHANNEL';
message_to_return :=
delete_from_table (step,
field_name_1,
field_name_2,
TABLE_NAME
);
IF LENGTH (message_to_return) > 0
THEN
RETURN message_to_return;
END IF;
.....
table_name := 'ACC_ACCOUNTDETAIL';
message_to_return :=
delete_from_table (step,
field_name_1,
field_name_2,
TABLE_NAME
);
RETURN message_to_return;
END;
FUNCTION delete_documents
RETURN VARCHAR2
IS
/* DECLARE */
table_name VARCHAR2 (30);
field_name VARCHAR2 (30) := 'documentid';
message_to_return VARCHAR2 (4000);
step VARCHAR2 (16) := 'DELETE DOCUMENTS';
BEGIN
message_to_return := EMPTY_CLOB ();
table_name := 'OA_STOREDDOCUMENT';
message_to_return :=
delete_from_table (step,
field_name,
NULL,
TABLE_NAME
);
IF LENGTH (message_to_return) > 0
THEN
RETURN message_to_return;
END IF;
table_name := 'OA_DOCACCOUNTINGDETAILRES';
message_to_return :=
delete_from_table (step,
field_name,
NULL,
TABLE_NAME
);
IF LENGTH (message_to_return) > 0
THEN
RETURN message_to_return;
END IF;
.....
table_name := 'OA_DOCUMENT';
message_to_return :=
delete_from_table (step,
field_name,
NULL,
TABLE_NAME
);
RETURN message_to_return;
END;
FUNCTION delete_notifications
RETURN VARCHAR2
IS
/* DECLARE */
table_name VARCHAR2 (30);
field_name VARCHAR2 (30) := 'id';
message_to_return VARCHAR2 (4000);
step VARCHAR2 (20) := 'DELETE NOTIFICATIONS';
BEGIN
message_to_return := EMPTY_CLOB ();
table_name := 'ESB_APCHEQUENOTIFICATION';
field_name := 'notificationid';
message_to_return :=
delete_from_table (step,
field_name,
NULL,
TABLE_NAME
);
IF LENGTH (message_to_return) > 0
THEN
RETURN message_to_return;
END IF;
table_name := 'ESB_ATTACHMENTNOTIFICATION';
field_name := 'id';
message_to_return :=
delete_from_table (step,
field_name,
NULL,
TABLE_NAME
);
IF LENGTH (message_to_return) > 0
THEN
RETURN message_to_return;
END IF;
.....
table_name := 'ESB_BOOKINGHOLDERNOTIFICATION';
message_to_return :=
delete_from_table (step,
field_name,
NULL,
TABLE_NAME
);
RETURN message_to_return;
END;
END;
/
The key of my problem is on delete_from_table function.
This function retrieves all rowid of records inside "table_name_param" table name that are in both environments (through a dblink to backup environemnt), identifying these records through the table key (parameters "field_name_1_param" and "field_name_2_param"). So, my query_chunk_generator statement is something like this:
SELECT ROWID, ROWID FROM OA_STOREDDOCUMENT WHERE documentid IN (select documentid from OA_STOREDDOCUMENT@dblink_to_backup) order by documentid desc
(in oder words, all records in backup database must be deleted from production environment)
So, the action to be performed by the task is a delete statement through rowid, like following:
DELETE FROM OA_STOREDDOCUMENT WHERE ROWID IN (:start_id,:end_id)
Here is my problem. Tables with million of records and lot of record to be deleted, assign only 1 chunk.
At the moment, the most big table has 60.897.786 records, and 239.215 of them must be deleted in next execution