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;
/

No comments:

Powered by Blogger.