DEV Community

mrcaption49
mrcaption49

Posted on • Edited 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)