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;