We have a large table in 10g that has been list partitioned. As time has passed data with a valid value that is not in any of the partitions' lists has been inserted many times. That data falls into the default partition
So we have something like this:
Table ERRORS
Partition E_1 values ('A', 'B', 'C')
Partition E_2 values ('L', 'M', 'N') - size 600G
Partition E_DEFAULT default (but contains data with only value 'Z') - size 50G
Now the vendor would like to correct this by adding the 'Z' values to E_2.
They propose the following:
alter table ERRORS split partition E_DEFAULT into (partition E_TEMP, partition E_DEFAULT);
alter table ERRORS merge partition E_TEMP, E_2 into partition E_2.
I am fine with the strategy. I have no doubt it will work.
I am concerned that because the partition sizes are so large the merge will require an extra 650G free in the tablespace. The vendors seem to think it will just add the 50G to the 600G.
Looking at the documentation, the merge partition section states:
"Use the ALTER TABLE ... MERGE PARTITION statement to merge the contents of two partitions into one partition. The two original partitions are dropped, as are any corresponding local indexes."
This implies to me that it takes the two and creates a third, thereby needing the 650G of extra free space.
But is that still the case if the third partition is actually one of the two being merged?
For what it's worth, my plan was:
Split default
Exchange the new partition with an empty temp table
drop the new partition
alter E_2 to add the new value
insert into ERRORS select * from temp table.
drop temp table
I think this requires 50G free space.
Thanks