DEV Community

Pranav Bakare
Pranav Bakare

Posted on

Automating Old Partition Cleanup in Oracle with PL/SQL

#automation #database #sql

Automating Old Partition Cleanup in Oracle with PL/SQL

  • 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;
Enter fullscreen mode Exit fullscreen mode

🔎 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
Enter fullscreen mode Exit fullscreen mode
  • 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');
Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode
  • 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.

Top comments (0)