partitions drop

 

--  THIS ONE WORKS – tested on Oracle 19c / 21c RDS

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name   => 'CLEANUP_OLD_PARTITIONS_01',

    job_type   => 'PLSQL_BLOCK',

    job_action => q'[

      DECLARE

        v_owner       CONSTANT VARCHAR2(128) := 'OWNER1';           -- CHANGE THIS

        v_table       CONSTANT VARCHAR2(128) := 'YOUR_TABLE_NAME'; -- CHANGE THIS

        v_truncated            PLS_INTEGER := 0;

      BEGIN

        FOR rec IN (

          SELECT partition_name

          FROM   dba_tab_partitions

          WHERE  owner       = v_owner

            AND  table_name  = v_table

            AND  partition_name NOT IN ('P_202511', 'P_202510')   -- KEEP these two

        )

        LOOP

          EXECUTE IMMEDIATE

            'ALTER TABLE ' || v_owner || '.' || v_table ||

            ' TRUNCATE PARTITION ' || rec.partition_name ||

            ' DROP STORAGE';

          v_truncated := v_truncated + 1;

          -- Optional: commit every 20 partitions so redo log doesn’t explode

          IF MOD(v_truncated, 20) = 0 THEN

            COMMIT;

          END IF;

        END LOOP;

        COMMIT;

        DBMS_OUTPUT.PUT_LINE('SUCCESS: Truncated ' || v_truncated || ' partitions from ' ||

                             v_owner || '.' || v_table);

      EXCEPTION

        WHEN OTHERS THEN

          ROLLBACK;

          DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);

          RAISE;

      END;

    ]',

    start_date => SYSTIMESTAMP,

    enabled    => TRUE,

    auto_drop  => TRUE,

    comments   => 'Fast background TRUNCATE of all old partitions except last 2'

  );

END;

/

============================

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name        => 'CLEANUP_OLD_PARTITIONS_01',

    job_type        => 'PLSQL_BLOCK',

    job_action      => q'[

      DECLARE

        v_cnt_dropped  PLS_INTEGER := 0;

        v_cnt_rebuilt  PLS_INTEGER := 0;

      BEGIN

        -- 1. Drop old partitions with PARALLEL 12 (fastest possible segment drop)

        FOR rec IN (

          SELECT partition_name

          FROM   user_tab_partitions

          WHERE  table_name = 'YOUR_TABLE_NAME'            -- CHANGE THIS

            AND  partition_name NOT IN ('P_202511', 'P_202510')  -- CHANGE THESE

        ) LOOP

          EXECUTE IMMEDIATE

            'ALTER TABLE YOUR_TABLE_NAME DROP PARTITION ' || rec.partition_name ||

            ' PARALLEL 12';

          v_cnt_dropped := v_cnt_dropped + 1;

        END LOOP;

        DBMS_OUTPUT.PUT_LINE('Dropped ' || v_cnt_dropped || ' partitions with PARALLEL 12');

        -- 2. Rebuild global indexes with PARALLEL 12

        FOR idx IN (

          SELECT index_name

          FROM   user_indexes

          WHERE  table_name = 'YOUR_TABLE_NAME'

            AND (status = 'UNUSABLE' OR partitioned = 'NO')

        ) LOOP

          EXECUTE IMMEDIATE

            'ALTER INDEX ' || idx.index_name || 

            ' REBUILD ONLINE PARALLEL 12';

          v_cnt_rebuilt := v_cnt_rebuilt + 1;

        END LOOP;

        DBMS_OUTPUT.PUT_LINE('Rebuilt ' || v_cnt_rebuilt || ' global indexes with PARALLEL 12');

      EXCEPTION

        WHEN OTHERS THEN

          DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);

          RAISE;

      END;]',

    start_date      => SYSTIMESTAMP,

    enabled         => TRUE,

    auto_drop       => TRUE,

    comments        => 'Ultra-fast partition purge - DROP + REBUILD both with PARALLEL 12'

  );

END;

/