How to split a very large partition of a Range-partitioned table into multiple partitions ?

posted Jul 14, 2011, 9:08 AM by Sachchida Ojha   [ updated Jul 14, 2011, 9:11 AM ]

You will need 500GB of free space to do the split of a 500GB partition.  Behind the scenes  we have to build two new segments for the two new partitions; that's the only way for us to physically separate the data belonging to the new target partitions.   If you don't have 500GB available,  here is an alternative you can do:

This process is going to take awhile to run, depending on how much data is involved.  We assume your table is called PRICE.   You should first test this on  a smaller table.

1. Do a Create Table As Select (CTAS) to preserve the smaller target partition data as separate table.

Create table PRICE_2 NOLOGGING PARALLEL(DEGREE 2) as select /*+ PARALLEL(A, 2) */ * from PRICE PARTITION (PRICE_MAXVALUE) where month_year between 200501 and 200512;

Check table PRICE_2 to ensure all the correct rows are there.

2. Then do a DELETE from the same data from the large original partition.

Delete from PRICE where month_year between 200501 and 200512;

3. Do a split of the original partition. This will be a fast split now since we figure out that there is no data for one of the new target partitions (we just deleted that data).

alter table PRICE split partition PRICE_MAXVALUE at (20090801) into (partition PRICE_2009_07, partition PRICE_MAXVALUE) parallel 2;

4. Do an exchange with the empty partitions and the standalone table created in step 1.

alter table PRICE exchange partition PRICE_2009_07 with table PRICE_2;

5.  Repeat steps 1-4 for each additional partition you want to create.

6. You can run this after each exchange. This 'shrink ' clause is valid only for segments in tablespaces with automatic segment management.

alter table PRICE modify partition PRICE_MAXVALUE shrink space;