2 Replies Latest reply on May 2, 2014 4:05 PM by rp0428

    Trying to partition already partitioned table using DBMS_REDEFINITION

    1052085

      Hi,

      I've a table with range partitioning. Now i've to change the table to list partition and then range subpartition. I'm trying to achieve it through DBMS_REDFINITION. But i'm getting the below error while redefining.

       

      DECLARE

      V_ERROR NUMBER;

      BEGIN

      DBMS_REDEFINITION.CAN_REDEF_TABLE ('SCHEMA1_2','DEMO', DBMS_REDEFINITION.CONS_USE_PK);

       

      DBMS_REDEFINITION.START_REDEF_TABLE ('SCHEMA1_2','DEMO', 'DEMO_PART', NULL, DBMS_REDEFINITION.CONS_USE_PK);

       

      DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCHEMA1_2','DEMO','DEMO_PART', COPY_INDEXES => DBMS_REDEFINITION.CONS_ORIG_PARAMS, COPY_TRIGGERS => TRUE, COPY_CONSTRAINTS => TRUE, COPY_PRIVILEGES => TRUE, IGNORE_ERRORS => FALSE, NUM_ERRORS => V_ERROR, COPY_STATISTICS => TRUE);

       

      SP_LOG('Errors Copying Table Dependents', V_ERROR);

       

      DBMS_REDEFINITION.FINISH_REDEF_TABLE ('SCHEMA1_2','DEMO', 'DEMO_PART');

      End;

      /

       

      ORA-14024: number of partitions of LOCAL index must equal that of the underlying table

      ORA-06512: at "SYS.DBMS_REDEFINITION", line 1364

      ORA-06512: at "SYS.DBMS_REDEFINITION", line 2026

       

      Do i need to drop all global and local indexes on the table to be redefined?

       

      Oracle Version: 11.2.0.2.0

       

      Thanks.

        • 1. Re: Trying to partition already partitioned table using DBMS_REDEFINITION
          Karthick2003

          Provide the structure of the existing table (DEMO) and the associated constraints and index. Provide the structure of the intermediate table (DEMO_PART).

          • 2. Re: Trying to partition already partitioned table using DBMS_REDEFINITION
            rp0428
            I've a table with range partitioning. Now i've to change the table to list partition and then range subpartition. I'm trying to achieve it through DBMS_REDFINITION. But i'm getting the below error while redefining.

             

            Have you reviewed and tried the examples in the DBA doc:

            http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#i1006859

             

            Why are you trying to do the operation online? I'm surprised that the CAN_REDEF procedure didn't raise an exception.

             

            The fact that you included the call to CAN_REDEF as part of your code tells me you didn't really test this process first. The FIRST thing you need to know is whether Oracle will even allow you to redefine your table. So it makes NO SENSE to include that test as part of the actual redefinition process EXCEPT for one reason - as a safeguard in case something/anything changed between your actual testing and the start of the redefinition.

             

            Post the results of a call to the CAN_REDEF procedure.

             

            Use an offline 'window' if at all possible for that type of change.

            1. EVERY ROW in EVERY PARTITION will need to be moved to a new segment.

            2. The performance is likely to be TERRIBLE due to #1 above especially if you have any indexes on the table.

            3. The execution plan for any queries on the table during the redefinition may be poor since it will be difficult for Oracle to find any given row

            4. After such a major reversal of partitioning strategies it will be important to backup the table IMMEDIATELY.

             

            If this is a critical production table I suggest you perform the operation offline AFTER extensive testing.