Database cleanup script for TIBCO CLEver

In our project I was asked to create a Oracle PL/SQL cleanup script for TIBCO''s CLEver monitoring tool which should be able to run online. Sharing is caring, so see below for the script. For robustness and performance I added a limit and bulk processing capabilities.

SET SERVEROUTPUT ON;

/**

* 

* Author: Joshua Moesa

* Date: 3rd Februari 2014

* Version: 3

* 

* This script deletes CLE data from the following tables:

* - TDA_TRANSACTIONID

* - TDA_LOGGING

* - TDA_LOGGINGLARGETEXT

* - TDA_EXCEPTION

* - TDA_EXCEPTIONLARGETEXT

*

**/

DECLARE

  /**

  * USER VARIABLES

  **/

  C_DELETE_BEFORE_DATE VARCHAR(10) := '1/01/2014'; --Delete all data BEFORE this date

  C_LIMIT PLS_INTEGER := 1000; --Use this record limit to avoid session memory errors

  

  /**

  * DECLARATIONS

  **/

  V_REC_COUNT NUMBER:=0;

  CURSOR LOGGINGTRAN_CUR

  IS

    SELECT ID

    FROM TDA_LOGGING TDAL

    JOIN 

      (SELECT TRANSACTIONID,

        MAX( DATETIME ) DATETIME

      FROM TDA_LOGGING

      GROUP BY TRANSACTIONID

      HAVING MAX(DATETIME) < TO_DATE(C_DELETE_BEFORE_DATE,'dd/MM/yyyy')

      ) TDAT ON TDAL.TRANSACTIONID = TDAT.TRANSACTIONID

    AND TDAT.TRANSACTIONID IS NOT NULL;

  CURSOR LOGGING_CUR

  IS

    SELECT ID FROM TDA_LOGGING WHERE DATETIME < TO_DATE(C_DELETE_BEFORE_DATE,'dd/MM/yyyy') AND TRANSACTIONID IS NULL;

  CURSOR TRANSACTION_CUR

  IS    

    SELECT DISTINCT TID.ID

    FROM TDA_TRANSACTIONID TID

    LEFT JOIN TDA_LOGGING TL ON TL.TRANSACTIONID = TID.ID

    WHERE TL.TRANSACTIONID IS NULL;

  CURSOR EXCEPTION_CUR

  IS

    SELECT ID FROM TDA_EXCEPTION WHERE DATETIME < TO_DATE(C_DELETE_BEFORE_DATE,'dd/MM/yyyy');

  TYPE TRANSACTION_T IS TABLE OF TRANSACTION_CUR%ROWTYPE;

  TYPE EXCEPTION_T IS TABLE OF EXCEPTION_CUR%ROWTYPE;

  TYPE LOGGING_T IS TABLE OF LOGGING_CUR%ROWTYPE;

  TYPE LOGGINGTRAN_T IS TABLE OF LOGGINGTRAN_CUR%ROWTYPE;

  L_TRANSACTION TRANSACTION_T;

  L_EXCEPTION EXCEPTION_T;

  L_LOGGING LOGGING_T;

  L_LOGGINGTRAN LOGGINGTRAN_T;

  

BEGIN

  DBMS_OUTPUT.PUT_LINE ('Start: ' || systimestamp() );

  

  /**

  * Delete Logging where max transaction date is before the given deletion date. 

  **/

  OPEN LOGGINGTRAN_CUR;

  LOOP

    FETCH LOGGINGTRAN_CUR BULK COLLECT INTO L_LOGGINGTRAN LIMIT C_LIMIT;

    EXIT WHEN L_LOGGINGTRAN.COUNT = 0;

    DBMS_OUTPUT.PUT_LINE ('Start deleting TDA_LOGGINGLARGETEXT records...');  

     

    FORALL I IN 1 .. L_LOGGINGTRAN.COUNT

      DELETE FROM TDA_LOGGINGLARGETEXT WHERE ID =  L_LOGGINGTRAN(I).ID;

    V_REC_COUNT := SQL%ROWCOUNT;      

    DBMS_OUTPUT.PUT_LINE ('Deleted ' || V_REC_COUNT || ' record(s).'); 

    V_REC_COUNT := 0;      

    DBMS_OUTPUT.PUT_LINE ('Start deleting TDA_LOGGING records...');  

    

    FORALL I IN 1 .. L_LOGGINGTRAN.COUNT

     DELETE FROM TDA_LOGGING WHERE ID = L_LOGGINGTRAN(I).ID;

    V_REC_COUNT := SQL%ROWCOUNT;      

    DBMS_OUTPUT.PUT_LINE ('Deleted ' || V_REC_COUNT || ' record(s).'); 

    V_REC_COUNT := 0;      

  

  END LOOP;

  

  /**

  * Delete Logging records where TRANSACTIONID is NULL.

  **/

  

  OPEN LOGGING_CUR;

  LOOP

    FETCH LOGGING_CUR BULK COLLECT INTO L_LOGGING LIMIT C_LIMIT;

    EXIT WHEN L_LOGGING.COUNT = 0;

    DBMS_OUTPUT.PUT_LINE ('Start deleting TDA_LOGGINGLARGETEXT records...'); 

    FORALL I IN 1 .. L_LOGGING.COUNT

      DELETE FROM TDA_LOGGINGLARGETEXT WHERE ID = L_LOGGING(I).ID;

      

    V_REC_COUNT := SQL%ROWCOUNT;      

    DBMS_OUTPUT.PUT_LINE ('Deleted ' || V_REC_COUNT || ' record(s).'); 

    V_REC_COUNT := 0;      

    DBMS_OUTPUT.PUT_LINE ('Start deleting TDA_LOGGING records...'); 

    

    FORALL I IN 1 .. L_LOGGING.COUNT

      DELETE FROM TDA_LOGGING WHERE ID = L_LOGGING(I).ID;

      

    V_REC_COUNT := SQL%ROWCOUNT;      

    DBMS_OUTPUT.PUT_LINE ('Deleted ' || V_REC_COUNT || ' record(s).'); 

    V_REC_COUNT := 0;

  END LOOP;

  /**

  * Delete transactionids.

  **/

  

  OPEN TRANSACTION_CUR;  

  

  LOOP

    FETCH TRANSACTION_CUR BULK COLLECT INTO L_TRANSACTION LIMIT C_LIMIT;

    EXIT WHEN L_TRANSACTION.COUNT = 0;

    DBMS_OUTPUT.PUT_LINE ('Start deleting TDA_TRANSACTIONID records...'); 

    FORALL I IN 1 .. L_TRANSACTION.COUNT

      DELETE FROM TDA_TRANSACTIONID WHERE ID = L_TRANSACTION(I).ID;  

      

    V_REC_COUNT := SQL%ROWCOUNT;      

    DBMS_OUTPUT.PUT_LINE ('Deleted ' || V_REC_COUNT || ' record(s).'); 

    V_REC_COUNT := 0;

  END LOOP;

  /**

  * Delete Exceptions

  **/

  OPEN EXCEPTION_CUR;

  LOOP

    FETCH EXCEPTION_CUR BULK COLLECT INTO L_EXCEPTION LIMIT C_LIMIT;

    EXIT WHEN L_EXCEPTION.COUNT = 0;

    DBMS_OUTPUT.PUT_LINE ('Start deleting TDA_EXCEPTION records...'); 

    

    FORALL I IN 1 .. L_EXCEPTION.COUNT

      DELETE FROM TDA_EXCEPTION WHERE ID = L_EXCEPTION(I).ID;

      

    V_REC_COUNT := SQL%ROWCOUNT;      

    DBMS_OUTPUT.PUT_LINE ('Deleted ' || V_REC_COUNT || ' record(s).'); 

    V_REC_COUNT := 0;

    DBMS_OUTPUT.PUT_LINE ('Start deleting TDA_EXCEPTIONLARGETEXT records...'); 

      

    FORALL I IN 1 .. L_EXCEPTION.COUNT

      DELETE FROM TDA_EXCEPTIONLARGETEXT WHERE ID = L_EXCEPTION(I).ID;

      

    V_REC_COUNT := SQL%ROWCOUNT;      

    DBMS_OUTPUT.PUT_LINE ('Deleted ' || V_REC_COUNT || ' record(s).'); 

    V_REC_COUNT := 0;

      

  END LOOP;

  CLOSE LOGGING_CUR;

  CLOSE LOGGINGTRAN_CUR;

--  CLOSE TRANSACTION_CUR;

  CLOSE EXCEPTION_CUR;

  DBMS_OUTPUT.PUT_LINE ('End: ' || systimestamp() );

  COMMIT;

  --ROLLBACK;

  

END;

/