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.

2 Answers 2


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

modify partition OCT19 
add subpartition OCT19AXCS 
values ('AXCS');
  • Where have you mentioned the subpartition key? What if the subpartition is to be made on other column? Mar 14, 2016 at 6:08

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 ....)
  • 1
    Any thoughts about creating a new table with desired partitions/subpartitions and then migrating data from current to new?
    – Raj
    Oct 30, 2013 at 12:30
  • yes, that is an obvious technique - but operationally more complex in terms of migration length and potential downtime. Oct 30, 2013 at 13:30
  • 1
    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.
    – Raj
    Oct 31, 2013 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.