Here is a slick way to do the job done. I created a procedure which expect two parameters;
- Name of the table
- Retention period (no. of days data to keep)
Hope you will find it useful.
create or replace PROCEDURE DROP_PARTITION_RANGEPART_PROC (P_TNAME IN VARCHAR2, P_RTNTNDAY IN INT DEFAULT NULL )
/*****************************************************************************
Purpose : This procedure takes two input parameter to drop PARTITION(S) in a Range partition table.
Range partition column value has to be in YYYYMMDD format.
P_TNAME : Table Name ex. RTL_SML_OUTSTANDING_DAILY_STG
P_RTNTNDAY : PARTITION Retain Days ex. 90
****************************************************************************/
AS
V_TABCNT INT := 0;
V_TNAME VARCHAR2(100);
V_ACTDATE NUMBER(38,0);
V_STG_TBL_DAYS_KEEP INT;
V_STG_TBL_DAYS INT;
v_SQL varchar2(4000);
V_PARTITION_NAME VARCHAR2(60);
TYPE cur_typ IS REF CURSOR;
c_cursor cur_typ;
BEGIN
V_TNAME := UPPER(P_TNAME);
IF P_RTNTNDAY IS NULL
THEN
V_STG_TBL_DAYS_KEEP := 30 ;
ELSE
V_STG_TBL_DAYS_KEEP := P_RTNTNDAY;
END IF;
BEGIN
dbms_output.put_line(‘START’);
V_TNAME := UPPER(P_TNAME);
V_SQL:= ‘ SELECT PARTITION_NAME FROM’||
‘ XMLTABLE( ”/ROWSET/ROW” PASSING DBMS_XMLGEN.GETXMLTYPE(”’||
‘ SELECT PARTITION_NAME, HIGH_VALUE,TABLE_NAME’||
‘ FROM ALL_TAB_PARTITIONS ” ) COLUMNS’||
‘ PARTITION_NAME VARCHAR2(30)’||
‘ ,HIGH_VALUE NUMBER(38,0),’||
‘ TABLE_NAME VARCHAR2(60) )’||
‘ WHERE HIGH_VALUE < ‘||
‘ (SELECT TO_NUMBER(TO_CHAR((TO_DATE(MAX(ACTIVITY_DT),”YYYYMMDD”) – :V_STG_TBL_DAYS_KEEP),”YYYYMMDD”))’||
‘ FROM ‘||V_TNAME||’ ) AND TABLE_NAME = :V_TNAME’||
‘ AND PARTITION_NAME <> ”PARTORG”’;
OPEN C_CURSOR FOR V_SQL USING V_STG_TBL_DAYS_KEEP,P_TNAME ;
LOOP
FETCH C_CURSOR INTO V_PARTITION_NAME;
EXIT WHEN C_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( ‘ALTER TABLE ‘ || V_TNAME||’ DROP PARTITION ‘||V_PARTITION_NAME);
EXECUTE IMMEDIATE ‘ALTER TABLE ‘ || V_TNAME||’ DROP PARTITION ‘||V_PARTITION_NAME;
END LOOP;
CLOSE C_CURSOR;
EXCEPTION
–TABLE DOES NOT EXIST
WHEN NO_DATA_FOUND THEN
–RAISE_APPLICATION_ERROR (-20001 , ‘PARTITION ‘||V_TNAME||’ DOES NOT EXIST, CHECK PARTITION AND TABLE NAME AND TRY AGAIN’);
NULL;
END;
END;