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