partitions

 

CREATE OR REPLACE PROCEDURE schema_name.p_cleanup_drop_columns

AS

    -- Define a collection to hold all your DDL statements

    TYPE t_ddl_list IS TABLE OF VARCHAR2(512);

    

    -- *** 1. ADD YOUR STATEMENTS HERE ***

    v_ddl_statements t_ddl_list := t_ddl_list(

        -- Placeholder 1: REPLACE this line

        'ALTER TABLE OWNER1.TABLE_INVENTORY DROP COLUMN OLD_FLAG_ID', 

        

        -- Placeholder 2: REPLACE this line

        'ALTER TABLE OWNER2.AUDIT_LOGS DROP COLUMN LEGACY_COL_DATE',

        

        -- Placeholder 3: REPLACE this line

        'ALTER TABLE OWNER3.CONFIG_DATA DROP COLUMN TEMP_VALUE',

        

        -- Add as many 'ALTER TABLE ... DROP COLUMN ...' statements as needed

        -- 'ALTER TABLE schema_name.table_name DROP COLUMN column_to_drop'

        

        -- Placeholder N: REPLACE this line

        'ALTER TABLE OWNER4.MASTER_TABLE DROP COLUMN REDUNDANT_KEY'

    );

    

    v_current_ddl VARCHAR2(512);

    

BEGIN

    -- Loop through the defined list of DDL statements

    FOR i IN 1..v_ddl_statements.COUNT LOOP

        v_current_ddl := v_ddl_statements(i);

        

        -- Begin an inner block to handle errors for THIS specific statement

        BEGIN

            -- Output the DDL being run (will be logged by DBMS_SCHEDULER)

            DBMS_OUTPUT.PUT_LINE('Executing: ' || v_current_ddl);

            

            -- Execute the DDL statement

            EXECUTE IMMEDIATE v_current_ddl;

            

            -- Commit implicitly happens due to DDL, but good to ensure transaction boundary

            COMMIT; 

            

            DBMS_OUTPUT.PUT_LINE('SUCCESS: ' || v_current_ddl);

            

        EXCEPTION

            WHEN OTHERS THEN

                -- Log the error, but do not raise the exception (continue the loop)

                DBMS_OUTPUT.PUT_LINE('*** ERROR *** Failed to execute DDL: ' || v_current_ddl);

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

                

                -- The NULL statement allows the procedure to proceed to the next item

                NULL; 

        END;

    END LOOP;

    

    DBMS_OUTPUT.PUT_LINE('Procedure p_cleanup_drop_columns finished processing ' || v_ddl_statements.COUNT || ' statements.');

    

END;

/

-- How to schedule the enhanced procedure

BEGIN

  DBMS_SCHEDULER.CREATE_JOB (

    job_name        => 'BACKGROUND_COLUMN_DROP_JOB',

    job_type        => 'STORED_PROCEDURE',

    job_action      => 'SCHEMA_NAME.P_CLEANUP_DROP_COLUMNS', -- The short, correct call

    enabled         => TRUE,

    auto_drop       => TRUE

  );

END;

/

==========

DECLARE
  -- *** Configuration Parameters (Same as yours) ***
  TYPE t_tab IS TABLE OF VARCHAR2(128);
  v_tables t_tab := t_tab(
    'OWNER.TABLE_N1',
    'OWNER.TABLE_N2',
    'OWNER.TABLE_N3',
    'OWNER.TABLE_N4',
    'OWNER.TABLE_N5'
  );
  v_max_boundary  NUMBER;
  v_start         NUMBER;
  v_sql           VARCHAR2(4000);
  
  -- *** New Iteration Variables ***
  v_new_boundary  NUMBER;
  v_new_pname     VARCHAR2(128);
  v_current_pname CONSTANT VARCHAR2(128) := 'REPORT_ID_7000'; -- The MAXVALUE partition
  
BEGIN
  -- 1. Find the current maximum boundary (Your logic is perfect here)
  SELECT MAX(TO_NUMBER(REGEXP_SUBSTR(high_value, '\d+')))
  INTO   v_max_boundary
  FROM   user_tab_partitions
  WHERE  table_name IN (
           SELECT UPPER(REGEXP_SUBSTR(column_value))
           FROM   TABLE(v_tables)
         )
    AND  partition_name LIKE 'REPORT_ID_%'
    AND  high_value NOT IN ('MAXVALUE', 'maxvalue') -- Exclude the MAXVALUE partition
    AND  high_value IS NOT NULL;
  IF v_max_boundary IS NULL THEN
    v_max_boundary := 270;
  END IF;
  v_start := v_max_boundary + 1;
  DBMS_OUTPUT.PUT_LINE('Highest existing boundary: ' || v_max_boundary);
  DBMS_OUTPUT.PUT_LINE('Will create partitions from ' || v_start || ' to ' || (v_start + 11));
  -- 2. Loop over each table
  FOR i IN 1..v_tables.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE('Processing table: ' || v_tables(i));
    -- 3. Loop 12 times to create 12 new partitions
    FOR j IN 0..11 LOOP
      v_new_boundary := v_start + j;
      v_new_pname := 'REPORT_ID_' || v_new_boundary;
      
      -- SPLIT PARTITION AT (new_boundary)
      -- This creates the new partition (P_XXX) up to the boundary,
      -- and leaves the remaining data (P_7000) to the right.
      v_sql := 'ALTER TABLE ' || v_tables(i) || ' SPLIT PARTITION ' || v_current_pname ||
               ' AT (' || (v_new_boundary + 1) || ')' || -- Boundary is always the next value (less than)
               ' INTO (PARTITION ' || v_new_pname || ' VALUES LESS THAN (' || (v_new_boundary + 1) || '), ' ||
               'PARTITION ' || v_current_pname || ')';
      -- Execute the DDL statement
      EXECUTE IMMEDIATE v_sql;
      DBMS_OUTPUT.PUT_LINE(' -> Created partition ' || v_new_pname);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('12 new partitions created successfully on ' || v_tables(i));
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE(' ');
  DBMS_OUTPUT.PUT_LINE('All done! 12 partitions added to ' || v_tables.COUNT || ' tables.');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Failing SQL: ' || v_sql);
    RAISE;
END;
/