Modifying Table Partitions in Oracle 12c

Up until now, we have been concentrating on creating tables and indexes with partitions. This lesson introduces some ways you can modify the partitions in a table.

Modifying the characteristics of partitions in a table in Oracle 12c is a crucial task for database administrators, especially when optimizing performance or managing large datasets. Here are the steps to follow in an authoritative, technical style:

1. Identify the Table and its Partitions: Before making any changes, you need to identify the table and its existing partitions. This can be done using the `DBA_TAB_PARTITIONS` or `USER_TAB_PARTITIONS` views. For example:
   

   SELECT table_name, partition_name, high_value
   FROM user_tab_partitions
   WHERE table_name = 'YOUR_TABLE_NAME';
   

2. Choose the Modification Operation: Depending on your requirement, you might need to perform one of several operations such as splitting a partition, merging partitions, adding a partition, dropping a partition, or modifying the partition's storage settings.
3. Splitting a Partition: To split a partition, use the `ALTER TABLE` statement with the `SPLIT PARTITION` clause. This is useful when a partition becomes too large and needs to be divided into smaller, more manageable segments. For example:
   

   ALTER TABLE your_table_name
   SPLIT PARTITION partition_name
   AT (partition_key_value)
   INTO (PARTITION new_partition_name1, PARTITION new_partition_name2);
   

4. Merging Partitions: If you need to combine two adjacent partitions, use the `MERGE PARTITIONS` clause. For example:
   

   ALTER TABLE your_table_name
   MERGE PARTITIONS partition_name1, partition_name2
   INTO PARTITION target_partition_name;
   

5. Adding a Partition: To add a new partition, the `ADD PARTITION` clause is used. This is particularly useful for range-partitioned tables where new data ranges are introduced. For example:
   

   ALTER TABLE your_table_name
   ADD PARTITION new_partition_name VALUES LESS THAN (value);
   

6. Dropping a Partition: To remove a partition and its data, use the `DROP PARTITION` clause. Be cautious with this operation as it permanently deletes the partition and its data. For example:
   

   ALTER TABLE your_table_name
   DROP PARTITION partition_name;
   

7. Modifying Partition Storage Attributes: To change storage characteristics of a partition, such as tablespace or PCTFREE, use the `MODIFY PARTITION` clause. For example:
   

   ALTER TABLE your_table_name
   MODIFY PARTITION partition_name
   STORAGE (INITIAL 50M NEXT 50M);
   

8. Review and Validate Changes: After performing the partition modifications, it’s crucial to review the changes for accuracy and performance impact. This can be done by querying the partition views mentioned in step 1.
9. Performance Considerations: Be aware that operations like splitting, merging, or dropping partitions can be resource-intensive and may impact database performance during execution. It’s advisable to perform such operations during off-peak hours.
10. Backup and Recovery Planning: Ensure that you have a proper backup and recovery plan in place before making any changes to the database structure to avoid data loss in case of errors.

Remember that while Oracle 12c offers advanced partitioning features, each modification operation should be carefully planned and executed considering the specific requirements and data distribution of your database.

The basic syntax for changing the partitioning in a table is the ALTER TABLE command. You can modify the number of partitions in a table using standard SQL syntax which is used to perform CRUD operations. There are four basic modifications you can make with the ALTER TABLE command, as the following table illustrates.

COMMANDS USED	ADVANTAGES
Add a partition	ALTER TABLE ADD PARTITION partition_name VALUES LESS THAN value storage_parameters	You may want to do this as the range of values for the partition key gets progressively higher. Keep in mind that you can create a partition table with only one partition in order to add partitions later with this command. You cannot add a partition to a table if the upper bound of the partition is MAXVALUE.
Move a partition	ALTER TABLE MOVE PARTITION tablespace	Moving a partition automatically creates a new segment for the partition and moves the data, even if it is in the same tablespace as the original partition. When you move a partition, all the indexes for the partition are marked as unusable and must be rebuilt.
Rename a partition	ALTER TABLE RENAME PARTITION original_partition_name TO new_partition_name
Modify a partition	ALTER TABLE MODIFY PARTITION	You can mark a local index as unusable, rebuild local indexes to make them usable, or to modify the storage attributes of a partition.

These four modifications are not the only ways to alter partitions--the next two lessons detail additional ways to change existing partitions.

You can use the alter table command to add, drop, exchange, move, modify, rename, split, and truncate partitions. These alter table command options allow you to alter the existing partition structure, as may be required after a partitioned table has been used heavily. For example, the distribution of the CategoryName values within the partitioned table may have changed, or the maximum value may have increased.
During an insert into the partitioned table, Oracle uses the definitions of partitions to determine which partition the record should be inserted into. Thus, you can use a partitioned table as if it were a single table, and rely on Oracle to manage the internal separation of the data. One common use of partitions allows you to minimize downtime in applications. Assume you have a table that is batch-loaded and partitioned by day. When a set of data arrives that is equal to one day comes in, you create a new table to store that data. Structure the new table to look like a partition of the existing partitioned table, load the data into the new table, then index it. This avoids the performance penalties associated with having the index in place during the data load. Next, analyze the new table. In the partitioned table, create a new partition for today’s data, then use the alter table exchange partition command to exchange that empty partition for the newly loaded table. By following this process you will be able to prevent the data load from adversely impacting the user access to the partitioned table while you load, index, and analyze it.
The next lesson shows how to drop a partition.