Manipulating Oracle Partitions

Oracle partitioning lets you manage very large tables as smaller, independently managed segments. In this lesson you’ll focus on three common maintenance operations: renaming a partition (metadata only), moving a partition (physical storage relocation), and coalescing (hash partition maintenance) / merging (range/list partition consolidation). These operations are widely used for reorganizing storage, enforcing naming conventions, and simplifying partition layouts as data ages.

1) Rename a partition

Renaming is a logical change: the partition’s segment and data remain where they are. This is commonly done when a partition name no longer reflects the values it contains (for example, after a business renaming or a convention change).

ALTER TABLE table_name
  RENAME PARTITION old_partition_name TO new_partition_name;

Example

ALTER TABLE sales
  RENAME PARTITION sales_q1 TO sales_2024_q1;

Example (legacy naming cleanup)

ALTER TABLE cd_library
  RENAME PARTITION elvis_era TO hank_williams_era;

2) Move a partition

Moving a partition relocates the partition segment to a different tablespace. This is used to:

• archive older partitions into lower-cost storage tablespaces,
• rebalance I/O across disks,
• defragment/reclaim space after heavy churn, or
• move partitions before performing tablespace maintenance.

ALTER TABLE table_name
  MOVE PARTITION partition_name TABLESPACE new_tablespace;

Example

ALTER TABLE sales
  MOVE PARTITION sales_q1 TABLESPACE sales_archive;

Index impact: moving a heap-table partition can leave local index partitions unusable unless you rebuild them, or you move/rebuild them as part of the operation. If you need Oracle to maintain index usability automatically, use UPDATE INDEXES where appropriate.

ALTER TABLE sales
  MOVE PARTITION sales_q1 TABLESPACE sales_archive
  UPDATE INDEXES;

If the partition contains LOB columns, you typically move the base table partition and also specify the LOB storage, because LOB segments may live in a different tablespace.

ALTER TABLE current_table
  MOVE PARTITION partition_name
  TABLESPACE destination_table_space
  LOB (column_name)
  STORE AS (TABLESPACE lob_tablespace);

3) Coalesce vs merge partitions

Coalesce (hash partitioned tables)

COALESCE PARTITION applies to hash partitioned tables. It reduces the number of hash partitions by taking one hash partition out of service and redistributing its rows across the remaining partitions (using the hash function). This is used after hash partitioning has been over-provisioned or when consolidating storage.

ALTER TABLE table_name
  COALESCE PARTITION;

Example

ALTER TABLE sales
  COALESCE PARTITION;

Merge partitions (range/list partitioned tables)

MERGE PARTITIONS is a different operation from coalesce and is commonly used with range or list partitioning. You specify exactly two adjacent partitions and combine them into one new partition. This is useful when the original partition boundaries were too granular and partitions have too few rows to justify their overhead.

ALTER TABLE tablename
  MERGE PARTITIONS partition1, partition2
  INTO PARTITION newpartition;

ALTER TABLE wholesale_order
  MERGE PARTITIONS low_part, med_part
  INTO PARTITION lowmed_part;

Putting it together

A common maintenance flow is to move older partitions to an archive tablespace, merge small adjacent partitions to reduce overhead, and rename the result to match your naming convention.

ALTER TABLE wholesale_order
  MOVE PARTITION low_part TABLESPACE low_part_ts;

ALTER TABLE wholesale_order
  MERGE PARTITIONS low_part, med_part
  INTO PARTITION low1_part;

ALTER TABLE wholesale_order
  RENAME PARTITION low1_part TO lowest_part;

Next, you’ll look at exchanging a partition with a table (a fast way to load or archive data with minimal movement).