All Products
Search

This Product

    PolarDB:ALTER TABLE RENAME PARTITION

    Document Center

    PolarDB:ALTER TABLE RENAME PARTITION

    Last Updated:Oct 23, 2025

    Renames a partition or subpartition. This is a metadata-only operation, but it can invalidate scripts that reference the legacy name. Before running this command, ensure the new name is unique and check for dependent code.

    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 be renamed.

    sp_asia

    new_name

    Yes

    The new name for the partition or subpartition.

    sp_2023_asia

    Notes

    • You must be the table owner or have the ALTER permission on the table.

    • RENAME PARTITION acquires a table-level exclusive lock AccessExclusiveLock on the target partition. This lock blocks all Data Manipulation Language (DML) and most Data Definition Language (DDL) operations on the table. Run this statement during off-peak hours and allow sufficient time for it to complete to avoid blocking other critical operations on the table.

    • The new partition or subpartition name must be unique within the table.

    • As a DDL operation, this command commits automatically and cannot be rolled back. To reverse the change, run another RENAME command to revert to the original name.

    • If the legacy partition name is hardcoded in application code, stored procedures, or views, these objects will fail after the rename. Before renaming a partition, perform a thorough impact analysis and update all dependent code.

    Examples

    This example shows how to rename the subpartition sp_asia in the composite partitioned table customer_data to sp_2023_asia to align with the naming convention based on year and region.

    Prepare the environment

    This step creates 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')
    )
);

    Perform a pre-check

    Verify that the target subpartition exists.

    -- Confirm that the old subpartition sp_asia exists
SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'CUSTOMER_DATA';

    Run the command

    Execute the RENAME SUBPARTITION command to rename the subpartition sp_asia to sp_2023_asia.

    ALTER TABLE customer_data RENAME SUBPARTITION sp_asia TO sp_2023_asia;

    Verify the result

    Verify that the subpartition was renamed.

    -- Structure verification: Confirm that the subpartition name is updated
SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'CUSTOMER_DATA';
-- The query result should include sp_2023_asia and not sp_asia

    FAQ

    Q1: Why do I get the error ORA-14078: partition name is already in use?
    This error indicates the specified new_name is already in use by another partition or subpartition in the same table. Choose a unique name.

    Q2: Why do I get the error ORA-14076: partition name must be specified?
    This error indicates that the specified old_name does not exist. Verify the name by following the pre-check steps.

    Q3: After renaming a partition, why do my applications or scripts that reference it stop working?
    This occurs when applications or scripts hardcode the old partition name. You must update all such references to use the new name.

    Related SQL statements