Error encountered when alter-switch is performed on a partitioned table

Create a table as defined in the target table;
Add a constraint to the table to ensure that the table data is in the target partition;
Alter table source table switch to target table partition Number
The partition number can be obtained through $ partition. partion_func (column value ).
This process is much faster than insert select because I/O is not involved. You only need to modify the metadata and the onwer of the partition side.

However, the above process only applies to the situation where the target partition already exists in the Partition Function of the target table. For example, if you define a partition function with a boundary of 1, 2, and 3, both 4 and 5 are inserted into the 4th partitions.

Therefore, we recommend that you use this method to define all partitions of the Partition Function in advance. If you want to partition by day, you should define 1000 partitions first. This method is relatively simple and does not need to be checked when data is added, resulting in errors.

If you must use dynamic partitions, You need to perform the following operations when adding a source table as a new partition:

First, obtain the value of the newly added partition column, and set it to X;
Use sys. Partition_range_values and sys. partition_functions to find all the boundary values of the target partition function;
Perform the following checks:
Here, the demarcation value belongs to the left partition or the right partition. We assume it belongs to the left partition;
If X exists in the two-step boundary value set, congratulations, no additional operations are required;
If X is greater than the maximum boundary in step 2, you not only need to split a partition for X, but also ensure that data smaller than X has its own partition.
Check the existing boundary values when adding partitions each time. If it is not equal to any of them, split with X as the parameter and delete the data with the same partition column and X in the target table.
Switch the source table to the target partition as described previously.
If data already exists in the target partition in step 2, the alter... switch statement fails because the target partition is not empty.

It can be understood that the switch operation is to modify the table metadata and replace the original allocation unit with a new allocation unit. If the original allocation unit is not empty, this replacement will cause some data loss and damage the data integrity. Therefore, it is not allowed.