Oracle® Retail Bulk Data Integration Cloud Service Implementation Guide Release 19.0.000 F25615-01 |
|
Previous |
Next |
The purge scripts helps in removing the old transactional data.
This script can run in two modes:
Silent Mode
Execute the procedure without passing any parameters, it calculates the period and remove the data. By default it keeps 6 months data and remove older than that. For example if some one wants to keep only 2 months of data and remove rest of the data, need to modify the procedure just by updating variable "howManyMonths NUMBER(5) := 6" to the required value.
howManyMonths = 2;
Interactive Mode
Pass the fromDate and toDate parameters to the procedure to purge the specified period of data. Data will be removed for only specified period i.e fromDate to toDate.
From and To dates format should be in below format:
FROMDATE := TO_TIMESTAMP('2017-01-01T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS');
TODATE := TO_TIMESTAMP('2017-04-30T23:59:59', 'YYYY-MM-DD"T"HH24:MI:SS');
The deleted data will be committed default in both silent and interactive modes.
Silent Mode
SET SERVEROUTPUT ON; DECLARE FROMDATE TIMESTAMP; TODATE TIMESTAMP; BEGIN FROMDATE := NULL; TODATE := NULL; BDI_PURGE_SQL.PURGE_JOB_INT_REPO ( FROMDATE => FROMDATE, TODATE => TODATE ); END;
Interactive Mode
SET SERVEROUTPUT ON; DECLARE FROMDATE TIMESTAMP; TODATE TIMESTAMP; BEGIN FROMDATE := TO_TIMESTAMP ('2017-01-03T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS'); TODATE := TO_TIMESTAMP ('2017-01-03T23:59:59', 'YYYY-MM-DD"T"HH24:MI:SS'); BDI_PURGE_SQL.PURGE_JOB_INT_REPO ( FROMDATE => FROMDATE, TODATE => TODATE ); END;
The purge sql removes the data from the following tables for the respective schema.
Schema Name | Sql Name | Table Name |
---|---|---|
job-int-schema |
purge_job_int_repo.sql |
BDI_DWNLDR_TRNSMITR_EXE_DSET BDI_DWNLDR_TRNSMITTR_TRANS |
job-rcvr-schema |
purge_job_rcvr_repo.sql |
BDI_RECVR_TRANSMISSION_BLOCK BDI_RECEIVER_TRANSMISSION BDI_RECEIVER_TRANSACTION |
batch-db-schema |
purge_batch_db_repo.sql |
JOBSTATUS STEPSTATUS STEPEXECUTIONINSTANCEDATA EXECUTIONINSTANCEDATA JOBINSTANCEDATA CHECKPOINTDATA |
process-schema |
purge_process_repo.sql |
BDI_ACTIVITY_EXEC_INSTANCE BDI_PROCESS_CALL_STACK BDI_PROCESS_EXEC_INSTANCE |
scheduler schema |
purge_scheduler_repo.sql |
BDI_SCHEDULE_EXECUTION |