How to alter an existing partitioned table to add sub-partitions to it? 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
  partition method
  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.
Scenario 1 - redefine the table

Your SQL is adding a sub-partition, not redefining the table.  I suspect you need to redefine the table to have sub-partitions before you can add a sub-partition.

12.2c+

The ability to change a non-partitioned table into a partitioned table via ALTER TABLE was introduced in 12.2c.

Looking at the documentation, it appears that you can change a RANGE partitioned table into a RANGE-LIST partition in the same way.  I haven't tried; be cautious; use DEV for practice.

pre-12.2c

DBMS_REDEFINITION is the de facto way of changing a table between non-partitioned and partition designs.

Google is your friend.  But, here is my recommended link:  

Scenario 2 - convert back to non-partitioned table.

This requirement perplexes me.  Doing such a massive change, multiple times, is probably a Tell-Tale sign that you have a more serious problem that you should be solving.

Any way, I suspect that the only way to do this is with DBMS_REDEFINITION.