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; |