This forum has helped me earlier on many occasions and now I need someone to help me with this.
I am looking to convert a pre-existing partitioned (range) table – to sub-partitioned (composite range-list).
1) I Range Partitioned an existing table using the query below:
alter table PART_TEST modify PARTITION BY RANGE (CREATEDATE) ( PARTITION p1 VALUES LESS THAN (TO_DATE('15-MAY-2019', 'DD-MON-YYYY')), PARTITION p2 VALUES LESS THAN (TO_DATE('16-MAY-2019', 'DD-MON-YYYY')), PARTITION p3 VALUES LESS THAN (maxvalue)) online;
2) Then I tried to alter the table to add sub-partitions (list) using the query below:
ALTER TABLE PART_TEST MODIFY PARTITION P1 ADD SUBPARTITION SP1 VALUES ('1') tablespace TEST_PART;
But I see this error:
Error report – ORA-14253: table is not partitioned by composite
14253. 00000 – "table is not partitioned by Composite Range method"
*Cause: The table in a subpartition maintenance operation (ALTER TABLE
EXCHANGE/MODIFY/MOVE/TRUNCATE SUBPARTITION, or ALTER TABLE MODIFY
PARTITION ADD/COALESCE SUBPARTITION command must be partitioned
by Composite Range method
*Action: Ensure that the table is partitioned by Composite Range method
Below is what I am tasked with,
Scenario – 1:
==> I want to have my existing partitions(range) subpartitioned (list) so that my old data is usable. The data in each of my partitions have to be moved to a sub-partition,
Scenario – 2: Then revert back to partition level (reverse migrate),
==> I mean the data from all of the sub-partitions within each partition is moved to be at the partition level and no sub-partitions are left in the database.
Is it possible and is there a way to do so?
If so how to implement the above scenarios – convert existing partitions(range) to sub-partitioned (Range-list) – and how to move back the data from sub-partitions to each of its partition back and no-sub partitions are left.