Adding subpartitions to existing partitioned table

Question

In Oracle 11g, is there a way to introduce subpartitions into an existing partitioned table?

I cannot seem to find a combination of EXCHANGE and SPLIT partition that does the right thing. SPLIT will split a partition into multiple partitions, not introduce subpartitions. Any suggestions?

I did find an existing post on partitioning an existing non-partitioned table by exchanging to a table with one partition and then using SPLIT, but can't figure out the equivalent process for subpartitions.

Answer 1

A new subpartition can be added to the existing partitioned table using the following command:

ALTER TABLE PART_TEST
modify partition OCT19 
add subpartition OCT19AXCS 
values ('AXCS');

• Some Java Guy: Where have you mentioned the subpartition key? What if the subpartition is to be made on other column?

Answer 2

I think I figured it out but it's tedious, requiring two temp tables and has to be done one partition at a time. Is there a better way?

For each partition in original (source) table

1. exchange partition to unpartitioned temp table (alter table source exchange partition X with table TEMP1)
2. exchange temp table into a second temp table, partitioned by same key as the subpartitions in the target table, with a single default partition (alter table TEMP2 exchange partition Y with table TEMP1)
3. exchange partitioned temp table into target table (alter table target exchange partition X with table TEMP2)
4. target table now has partition X with subpartition Y - split subpartition Y into desired subpartitions (alter table target split subpartition Y ....)

• Raj: Any thoughts about creating a new table with desired partitions/subpartitions and then migrating data from current to new?
• wrschneider: yes, that is an obvious technique - but operationally more complex in terms of migration length and potential downtime.
• Raj: You dont say why you are doing partitioning, but if it is to maintain historical data, you can implement new partitioning along with existing, go on migrating old data. When time comes, do the latest partitions, flip names, manage index/constraints/synonyms/grants etc and you are done. Thus you can reduce final downtime considerably. Of course this may not work in every environment and if you keep updating historical data. So, YMMV, but just wanted to point out that it is doable if your business process and environment supports it.