CREATE OR REPLACE PROCEDURE G_LOG_CLEANUP (daysToKeep IN NUMBER, runLimit IN NUMBER) AS --************************************************************************* --Descripition: Procedure to clean up Genesys DB Log server records -- Tables Affected: -- GENLOG.G_LOG_ATTRS -- GENLOG.G_LOG_MESSAGES --Inputs: --  daysToKeep - Number of days to keep in the tables --  timeLimit - Number of minutes before the query exits. Time is checked after -- each delete loop. Lower the recordLimit variable to decrease the time spent -- within each loop iteration -- --Written By: Todd McCall --Date: 10/20/2014 --**************************************************************************** --Variables recDate GENLOG.G_LOG_MESSAGES.TIMEGENERATED%type; startTime date:= SYSDATE; elapsedTime number:=0; recDateLimit date:=SYSDATE; --Set the date limit for deleting records recordCount number:=0; recordLimit number:=100000; --Set this value lower if the temp file for rollback is too large or the delete takes too long minRunLimit number:= runLimit; timeElapsed char(10); n_daysToKeep number := daysToKeep; BEGIN If runLimit > 1440 then minRunLimit:=1440; --Maximum limit for a query is 24 hours end if; If daysToKeep < 10 then n_daysToKeep:=10; --Minimum number of days to keep is 10 end if; recDateLimit :=SYSDATE-n_daysToKeep; --Convert minutes into an oracle calendar day value minRunLimit := (minRunLimit * .00069444); -- 1 = one day || .041667 = 1 hour || .00069444 = 1 minute || .000011574074 = 1 second dbms_output.put_line(chr(10) || 'Start Time: ' || to_char(startTime, 'MM-DD-YYYY hh24:mi:ss')); dbms_output.put_line('Days To Keep: ' || n_daysToKeep); dbms_output.put_line('Record delete size: ' || recordLimit); dbms_output.put_line('Run Limit-minutes: ' || (minRunLimit/.00069444)); --Set the oldest record date select min(GENLOG.G_LOG_MESSAGES.TIMEGENERATED) into recDate from GENLOG.G_LOG_MESSAGES where GENLOG.G_LOG_MESSAGES.PRIORITY in (2,3,4,5) AND GENLOG.G_LOG_MESSAGES.CATEGORY in (0); dbms_output.put_line(chr(10) || 'Oldest Record Date: ' || to_char(recDate, 'MM-DD-YY hh24:mi:ss')); --Set the record count for targeted records select count(*) into recordCount from GENLOG.G_LOG_MESSAGES where GENLOG.G_LOG_MESSAGES.TIMEGENERATED < recDateLimit and GENLOG.G_LOG_MESSAGES.PRIORITY in (2,3,4,5) and GENLOG.G_LOG_MESSAGES.CATEGORY in (0); dbms_output.put_line(chr(10) || 'Initial count - records to delete: ' || recordCount); dbms_output.put_line(chr(10) || 'DELETING...'); --START THE LOOP - exit the loop when the oldest record date is younger than DaysToKeep OR when the time has run out WHILE recDate < recDateLimit AND elapsedTime <= minRunLimit LOOP --This delete query selects all records in the G_LOG_ATTRS table where the LRID field is found in the next subquery delete from GENLOG.G_LOG_ATTRS where GENLOG.G_LOG_ATTRS.LRID in ( -- This subquery returns a table view with one column, ID, where the r_num of the table its querying is less than recordLimit select ID from( --This subquery returns a table view with an ID column and an r_num column, with criteria applied to the TIMEGENERATED, PRIORITY and CATEGORY columns of the G_LOG_MESSAGES table select GENLOG.G_LOG_MESSAGES.ID , ROW_NUMBER() OVER (order by GENLOG.G_LOG_MESSAGES.ID) as r_num from GENLOG.G_LOG_MESSAGES where GENLOG.G_LOG_MESSAGES.TIMEGENERATED <= recDateLimit AND GENLOG.G_LOG_MESSAGES.PRIORITY in (2,3,4,5) AND GENLOG.G_LOG_MESSAGES.CATEGORY in (0) ) where r_num < recordLimit ); delete from GENLOG.G_LOG_MESSAGES where GENLOG.G_LOG_MESSAGES.ID in ( select ID from( select GENLOG.G_LOG_MESSAGES.ID , ROW_NUMBER() OVER (order by GENLOG.G_LOG_MESSAGES.ID) as r_num from GENLOG.G_LOG_MESSAGES where GENLOG.G_LOG_MESSAGES.TIMEGENERATED <= recDateLimit AND GENLOG.G_LOG_MESSAGES.PRIORITY in (2,3,4,5) AND GENLOG.G_LOG_MESSAGES.CATEGORY in (0) ) where r_num < recordLimit ); commit; --This query will break the WHILE LOOP, by changing the value of recDate select min(GENLOG.G_LOG_MESSAGES.TIMEGENERATED) into recDate from GENLOG.G_LOG_MESSAGES where GENLOG.G_LOG_MESSAGES.PRIORITY in (2,3,4,5) AND GENLOG.G_LOG_MESSAGES.CATEGORY in (0); --This variable also breaks the WHILE LOOP elapsedTime := (SYSDATE-startTime); end LOOP; dbms_output.put_line(' ...FINISHED!' || chr(10)); select count(*) into recordCount from GENLOG.G_LOG_MESSAGES where GENLOG.G_LOG_MESSAGES.TIMEGENERATED < recDateLimit AND GENLOG.G_LOG_MESSAGES.PRIORITY in (2,3,4,5) AND GENLOG.G_LOG_MESSAGES.CATEGORY in (0); select min(GENLOG.G_LOG_MESSAGES.TIMEGENERATED) into recDate from GENLOG.G_LOG_MESSAGES where GENLOG.G_LOG_MESSAGES.PRIORITY in (2,3,4,5) AND GENLOG.G_LOG_MESSAGES.CATEGORY in (0); timeElapsed := TO_CHAR(EXTRACT(HOUR FROM NUMTODSINTERVAL(SYSDATE-startTime, 'DAY')), 'FM00') || ':' || TO_CHAR(EXTRACT(MINUTE FROM NUMTODSINTERVAL(SYSDATE-startTime, 'DAY')), 'FM00') || ':' || TO_CHAR(EXTRACT(SECOND FROM NUMTODSINTERVAL(SYSDATE-startTime, 'DAY')), 'FM00'); If elapsedTime >= minRunLimit then dbms_output.put_line('ELAPSED TIME EXCEEDED TIME LIMIT!!' || chr(10) || 'Limit: ' || runLimit || ' minutes.' || chr(10) || 'Elapsed: ' || timeElapsed); end if; dbms_output.put_line('Remaining records after delete: ' || recordCount || chr(10) || 'Elapsed time: ' || timeElapsed ); END;