Oracle procedure to delete old records from B2B Integrator correlation_set table

This Oracle procedure will delete old records from the correlation_set table that are older than a configured number of days (100 in the code below). It will perform the deletes in small batches and commits each time to minimize the impact on the Database. This procedure can run without stopping the B2B Integrator application.

Before running this procedure, check first if you have old records in correlation_set with this SQL query that returns the distribution of records by month and year:

select count(*),to_char(rec_time,'YYYYMM') from correlation_set group by to_char(rec_time,'YYYYMM') order by to_char(rec_time,'YYYYMM') desc;

If there are old records in the table you can use the procedure. Remember to create the CLEANUP_DEBUG table first to track the procedure progress:

create TABLE CLEANUP_DEBUG(ROWS_DELETED NUMBER,TOTAL NUMBER,START_TIME TIMESTAMP,END_TIME TIMESTAMP,DESCRIPTION VARCHAR2(60)); 

Oracle PL/SQL Procedure source:

Delete old records from Correlation_set table:


-- PLSQL PROCEDURE TO DELETE OLD RECORDS FROM CORRELATION_SET TABLE that are older than specified number of days.
-- This procedure will delete all records older than 100 days in 10k small batches.
-- 

-- DDL TO CREATE DEBUGGING TABLE -----> PLEASE RUN FOR THE FIRST TIME ONLY: ------->
-- create TABLE CLEANUP_DEBUG(ROWS_DELETED NUMBER,TOTAL NUMBER,START_TIME TIMESTAMP,END_TIME TIMESTAMP,DESCRIPTION VARCHAR2(60)); 
-- to clean the debug table add 
-- truncate table CLEANUP_DEBUG; 
DECLARE   
 nCount  NUMBER:=0;   
 iCount  NUMBER:=0; 

  
 -- to delete all the records older than timeLimit number of days 
 timeLimit NUMBER:=100; 

   batchSize NUMBER:=10000; 
 sql1 VARCHAR2(2000);   
 starttime timestamp; 

BEGIN 
     
     sql1 := 'delete from CORRELATION_SET where  REC_TIME < sysdate -' || timeLimit || '  and rownum <= ' || batchSize;   
 LOOP   
starttime:=sysdate; 
EXECUTE IMMEDIATE sql1;   

   nCount := sql%rowcount;   
    iCount:=iCount+nCount; 
      insert into CLEANUP_DEBUG values (batchSize,iCount,starttime, sysdate,'correlation_cleanup 1');   
   commit;       
   EXIT WHEN nCount = 0;   
 END LOOP;   
end;   



 

To monitor the procedures progress, check the CLEANUP_DEBUG table (create script shown in the procedure comments).

Run this SQL query:
Select * from cleanup_debug order by end_time desc;

Related links:

Oracle Procedure to delete old records from TRANS_DATA.

Delete Old records from WORKFLOW_LINKAGE table.