Provide the structure of the existing table (DEMO) and the associated constraints and index. Provide the structure of the intermediate table (DEMO_PART).
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:
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.