Rename Table Partitions in PolarDB with ALTER TABLE RENAME PARTITION - PolarDB

Renames a partition or subpartition. This is a metadata-only operation, but it can invalidate any application code, stored procedures, or views that reference the old name.

Synopsis

ALTER TABLE table_name RENAME PARTITION old_name TO new_name;

ALTER TABLE table_name RENAME SUBPARTITION old_name TO new_name;

Parameters

Parameter	Required	Description	Example
`table_name`	Yes	The name of the target partitioned table.	`customer_data`
`old_name`	Yes	The current name of the partition or subpartition to rename.	`sp_asia`
`new_name`	Yes	The new name for the partition or subpartition.	`sp_2023_asia`

Usage notes

To run this command, you must be the table owner or have the ALTER permission on the table.
RENAME PARTITION acquires a table-level AccessExclusiveLock on the target partition. This lock blocks all Data Manipulation Language (DML) and most Data Definition Language (DDL) operations. Run this command during off-peak hours to avoid blocking other operations on the table.
The new name must be unique within the table — no other partition or subpartition can share the same name.
This command is a DDL operation: it commits automatically and cannot be rolled back. To reverse the rename, run another RENAME command with the original name.
If application code, stored procedures, or views reference the old partition name, those objects will fail after the rename. Perform a thorough impact analysis and update all dependent references before renaming.

Example

This example renames the subpartition sp_asia to sp_2023_asia in a RANGE-LIST composite partitioned table named customer_data.

Prepare the environment

Create a RANGE-LIST composite partitioned table named customer_data.

-- Create a composite partitioned table
CREATE TABLE customer_data (
    customer_id INT, region VARCHAR2(20), reg_date DATE
)
PARTITION BY RANGE (reg_date)
SUBPARTITION BY LIST (region) (
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) (
        SUBPARTITION sp_asia VALUES ('Asia'),
        SUBPARTITION sp_2023_europe VALUES ('Europe')
    )
);

Check the current state

Confirm that the subpartition sp_asia exists before renaming.

SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'CUSTOMER_DATA';

Expected output:

SUBPARTITION_NAME
-----------------
SP_ASIA
SP_2023_EUROPE

Run the rename

ALTER TABLE customer_data RENAME SUBPARTITION sp_asia TO sp_2023_asia;

Verify the result

Run the same query to confirm the rename took effect.

SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS
WHERE TABLE_NAME = 'CUSTOMER_DATA';

Expected output:

SUBPARTITION_NAME
-----------------
SP_2023_ASIA
SP_2023_EUROPE

sp_asia no longer appears, and sp_2023_asia takes its place.

FAQ

Why do I get ORA-14078: partition name is already in use?

The new_name you specified is already used by another partition or subpartition in the same table. Choose a unique name.

Why do I get ORA-14076: partition name must be specified?

The old_name you specified does not exist. Run the pre-check query in Check the current state to verify the partition name.

After renaming, why do my applications or scripts stop working?

The old partition name is hardcoded in those applications or scripts. Update all references to use the new name.

What's next

ALTER TABLE ADD PARTITION: Adds a new partition to a partitioned table.
ALTER TABLE MERGE PARTITION: Merges two contiguous partitions into a single partition.
ALTER TABLE SPLIT PARTITION: Splits one partition into two partitions.
ALTER TABLE DROP PARTITION: Deletes a specified partition from a partitioned table.