Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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. Thanks!

share|improve this question
add comment

2 Answers

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

Subpartition to the existing partitioned table can be added using below alter table command. It works fine. ALTER TABLE PART_TEST modify partition OCT19 add subpartition OCT19AXCS values ('AXCS');

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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