Automating Old Partition Cleanup in Oracle with PL/SQL

This content originally appeared on DEV Community and was authored by Pranav Bakare

This PL/SQL block is designed to automate partition maintenance for all tables owned by the schema CMN_OWNR. It loops through each table partition (skipping ones with INIT in their name), extracts the partition boundary date from the high_value column, and checks if it is on or before 1st June 2025.
If the condition is met, it tries to drop the partition along with updating indexes; however, if the partition cannot be dropped due to being the last one (ORA-14758), it instead truncates the partition to remove data while keeping structure intact.
This ensures that only old partitions are purged while preserving the latest required ones. In short, it’s a safe cleanup mechanism for managing historical data in partitioned tables.

DECLARE
  v_date DATE;
  v_sql  VARCHAR2(4000);
BEGIN
  FOR i IN (SELECT dp.table_name,
                   dp.partition_name,
                   dp.high_value,
                   dp.partition_position
              FROM dba_tab_partitions dp
             WHERE dp.table_owner = 'CMN_OWNR'
               and dp.partition_name not like '%INIT%'
            --AND table_name = 'CMN_LOG'
             ORDER BY dp.table_name, partition_position) LOOP
    v_date := TO_DATE(substr(i.high_value, 12, 10), 'RRRR-MM-DD');
    IF v_date <= to_date('01-JUN-2025', 'dd-mon-yyyy') THEN
      v_sql := 'ALTER TABLE CMN_OWNR.' || i.table_name ||
               ' DROP PARTITION ' || i.partition_name || ' UPDATE INDEXES';
      BEGIN
        EXECUTE IMMEDIATE v_sql;
      EXCEPTION
        WHEN OTHERS THEN
          IF SQLCODE = -14758 THEN
            v_sql := 'ALTER TABLE CMN_OWNR.' || i.table_name ||
                     ' TRUNCATE PARTITION ' || i.partition_name ||
                     ' UPDATE INDEXES';
            EXECUTE IMMEDIATE v_sql;
          ELSE
            dbms_output.put_line(SQLERRM);
            dbms_output.put_line(v_sql);
          END IF;
      END;
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;

Step-by-step explanation

1. Loop through all partitions

FOR i IN (
   SELECT dp.table_name,
          dp.partition_name,
          dp.high_value,
          dp.partition_position
   FROM dba_tab_partitions dp
   WHERE dp.table_owner = 'CMN_OWNR'
     AND dp.partition_name NOT LIKE '%INIT%'
   ORDER BY dp.table_name, partition_position
) LOOP

Finds all partitions of all tables owned by CMN_OWNR.
Skips partitions whose name contains INIT.
Loops through each partition in order.

2. Extract partition boundary date

v_date := TO_DATE(SUBSTR(i.high_value, 12, 10), 'RRRR-MM-DD');

dba_tab_partitions.high_value is a text expression like:
TO_DATE(‘ 2025-06-01 00:00:00′,’SYYYY-MM-DD HH24:MI:SS’, …)
SUBSTR(…,12,10) picks 2025-06-01.
TO_DATE(…,’RRRR-MM-DD’) converts that to an Oracle DATE → 01-JUN-2025.

3. Check if partition is old

IF v_date <= TO_DATE('01-JUN-2025','DD-MON-YYYY') THEN

If the partition’s upper boundary date is before or equal to 01-JUN-2025, it’s considered old → purge it.

4. Try to drop the partition

v_sql := 'ALTER TABLE CMN_OWNR.' || i.table_name ||
         ' DROP PARTITION ' || i.partition_name || 
' UPDATE INDEXES';
EXECUTE IMMEDIATE v_sql;

Builds dynamic SQL to drop the old partition.
UPDATE INDEXES ensures local/global indexes remain usable.

5. Handle errors

EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -14758 THEN
      v_sql := 'ALTER TABLE CMN_OWNR.' || i.table_name ||
               ' TRUNCATE PARTITION ' || i.partition_name ||
               ' UPDATE INDEXES';
      EXECUTE IMMEDIATE v_sql;
    ELSE
      dbms_output.put_line(SQLERRM);
      dbms_output.put_line(v_sql);
    END IF;

If DROP fails with ORA-14758 (cannot drop the last partition in a range), it falls back to:
ALTER TABLE … TRUNCATE PARTITION …
→ keeps the partition structure but deletes its data.
Any other error is logged with dbms_output.

6. Global error handling

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;

If the whole block fails for some reason, the error is printed.

Summary —

This PL/SQL script automatically manages old partitions in Oracle tables owned by CMN_OWNR. It checks each partition’s HIGH_VALUE date and, if it is on or before 01-JUN-2025, it attempts to drop the partition. If dropping is not allowed (SQL error -14758), it instead truncates the partition while updating indexes. This helps keep partitioned tables lean by cleaning up historical data without manual intervention.

This content originally appeared on DEV Community and was authored by Pranav Bakare