| Lesson 10 | Splitting and merging partitions |
| Objective | Split and merge partitions. |
Splitting Merging Oracle Partitions
You create partitioned tables in order to increase performance and availability and reduce maintenance time.
However, changing conditions or an incorrect initial design may require you to alter the way the partitions are divided. If this occurs, one option is to either split one partition into two, or merge two partitions into one.
Splitting Partitions
You can split a partition into two separate partitions with the command:
ALTER TABLE table_name SPLIT PARTITION partition_name
AT ( '40-001' )
INTO ( PARTITION new_partition_name1
PARTITION new_partition_name2)
This command will also split the associated local index partition. All affected index partitions, both local and global, will be marked as unusable and will have to be rebuilt.
Merging partitions
Merging partitions is not as simple as the other partitioning modifications discussed so far. There is not a single command that will merge partitions. Instead, you must go through a four-step process:
- Dump the data from the two partitions to be merged into a dump file
- Drop the partitions you want to merge from the table
- Add the new partitions you want to create to the table
- Reload the data from the dump file
As with the modifications above, any local indexes will be automatically dropped and recreated, and any affected global index partitions or any nonpartitioned indexes will be marked as unusable.
Merging Partitions Carousel
The following Slide Show illustrates the process of merging partitions:
- We will start with the same partitioned table.
- The first step is to dump the data from the partitions that will be merged.
- The next step is to drop the partitions that will be merged. This action will also drop the partitions of the local index.
- The next step is to create a new partition. This action will also create the corresponding index partition for the local index.
- Once the partition is created, you reload the dumped data into the table, which will also create the index entries.
