Modifying Table Partitions in Oracle 23ai

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 23ai is a routine task for database administrators, especially when optimizing performance or managing large datasets. The general process:

1. Identify the Table and its Partitions: Before making any changes, identify the table and its existing partitions 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 a partition's storage settings.
3. Splitting a Partition: Use the ALTER TABLE statement with the SPLIT PARTITION clause. 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: 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: Use the ADD PARTITION clause. 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: Use the DROP PARTITION clause to remove a partition and its data. Be cautious, since this 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 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 partition modifications, review them for accuracy and performance impact by querying the partition views mentioned in step 1.
9. Performance Considerations: Operations like splitting, merging, or dropping partitions can be resource-intensive and may impact database performance during execution. Perform such operations during off-peak hours where possible.
10. Backup and Recovery Planning: Ensure 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.

Each modification operation should be carefully planned and executed with the specific requirements and data distribution of your database in mind.

The basic syntax for changing the partitioning in a table is the ALTER TABLE command. There are four basic modifications you can make with it, as the following table illustrates.

Commands Used	Example	Notes
Add a partition	ALTER TABLE your_table_name 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 partitioned 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 last partition is MAXVALUE.
Move a partition	ALTER TABLE your_table_name MOVE PARTITION partition_name TABLESPACE tablespace_name;	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 indexes for the partition are marked as unusable and must be rebuilt.
Rename a partition	ALTER TABLE your_table_name RENAME PARTITION original_partition_name TO new_partition_name;	Renaming does not move any data or affect index status. It only changes the name used to reference the partition in subsequent DDL.
Modify a partition	ALTER TABLE your_table_name MODIFY PARTITION partition_name UNUSABLE LOCAL INDEXES;	You can mark a local index as unusable, rebuild local indexes to make them usable again, or 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.

After any move, split, merge, or modify operation, it is worth confirming the change actually took effect before moving on. USER_TAB_PARTITIONS shows the current partition list, and USER_IND_PARTITIONS shows whether any local index partitions were marked UNUSABLE as a side effect, most commonly after a MOVE PARTITION:

SELECT partition_name, tablespace_name
FROM   user_tab_partitions
WHERE  table_name = 'YOUR_TABLE_NAME'
ORDER BY partition_position;

SELECT index_name, partition_name, status
FROM   user_ind_partitions
WHERE  status = 'UNUSABLE';

Any index partitions returned by the second query need an ALTER INDEX ... REBUILD PARTITION before that index can be relied on again.

You can use the ALTER TABLE command to add, drop, exchange, move, modify, rename, split, and truncate partitions. These 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 a partitioned table, Oracle uses the partition definitions to determine which partition the record should be inserted into. This lets you use a partitioned table as if it were a single table, while Oracle manages the internal separation of the data. One common use of partitioning is minimizing downtime during batch loads. Assume you have a table that is batch-loaded and partitioned by day. As a day's worth of new data arrives, you create a separate staging table structured to look like a partition of the existing partitioned table, load the data into it, and index it. This avoids the performance penalty of maintaining an index during the load itself. Next, you analyze the staging table, create a new empty partition for the day in the partitioned table, and use EXCHANGE PARTITION to swap the loaded staging table into place as that partition:

ALTER TABLE your_table_name
EXCHANGE PARTITION new_partition_name
WITH TABLE staging_table_name
INCLUDING INDEXES
WITHOUT VALIDATION;

Because this is a metadata-only operation rather than a data copy, it completes almost instantly regardless of how much data is in the staging table, preventing the load from adversely impacting user access to the partitioned table while it was being loaded, indexed, and analyzed.
The next lesson shows how to drop a partition.