All Products
Search

This Product

    PolarDB:ALTER TABLE DROP SUBPARTITION

    Document Center

    PolarDB:ALTER TABLE DROP SUBPARTITION

    Last Updated:Mar 11, 2026

    Drops a subpartition and its data from a composite partitioned table. This operation permanently removes the subpartition data. Before you run this statement, ensure the data is backed up or no longer needed.

    Synopsis

    ALTER TABLE table_name DROP SUBPARTITION subpartition_name;

    Parameters

    Parameter

    Required

    Description

    Example

    table_name

    Yes

    The name of the composite partitioned table that contains the subpartition to be dropped.

    customer_data

    subpartition_name

    Yes

    The name of the subpartition to be dropped.

    p_2022_asia

    Notes

    • You must be the table owner or a privileged user to run this command.

    • You cannot drop the last subpartition in a table.

    • DROP SUBPARTITION acquires a table-level exclusive lock AccessExclusiveLock. 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.

    Examples

    This example drops the subpartition for user data from the Asia region for the year 2022 to free up storage space. The customer_data table is a composite partitioned table that is partitioned by registration year (range partitioning) and subpartitioned by region (list subpartitioning).

    Prepare the environment

    This step creates a composite partitioned table named customer_data. The table is range-partitioned by registration year and then list-subpartitioned by user region.

    -- Create a composite partitioned table
CREATE TABLE customer_data (
    customer_id  INT NOT NULL,
    region       VARCHAR2(10),
    reg_date     DATE
)
PARTITION BY RANGE (reg_date)
SUBPARTITION BY LIST (region)
(
    PARTITION p_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION p_2022_asia    VALUES ('Asia'),
        SUBPARTITION p_2022_europe  VALUES ('Europe')
    ),
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION p_2023_asia    VALUES ('Asia'),
        SUBPARTITION p_2023_europe  VALUES ('Europe')
    )
);

-- Insert data
INSERT INTO customer_data VALUES (101, 'Asia', TO_DATE('2022-06-10', 'YYYY-MM-DD'));
INSERT INTO customer_data VALUES (102, 'Europe', TO_DATE('2022-07-20', 'YYYY-MM-DD'));
INSERT INTO customer_data VALUES (103, 'Asia', TO_DATE('2023-06-10', 'YYYY-MM-DD'));
INSERT INTO customer_data VALUES (104, 'Europe', TO_DATE('2023-07-20', 'YYYY-MM-DD'));

    Perform the pre-check

    Before dropping the subpartition, back up its data and verify that it exists.

    -- Important: Before you drop the subpartition, make sure that you have backed up the relevant data.

-- Check the subpartition structure to confirm that the p_2022_asia subpartition exists.
SELECT SUBPARTITION_NAME 
FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'CUSTOMER_DATA';

    Run the command

    Run the DROP SUBPARTITION command to drop the p_2022_asia subpartition.

    ALTER TABLE customer_data DROP SUBPARTITION p_2022_asia;

    Verify the result

    1. Verify that the subpartition and its data have been dropped.

      -- Structure verification: Confirm that the subpartition is dropped.
SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS 
WHERE TABLE_NAME = 'CUSTOMER_DATA';
-- The query result no longer contains p_2022_asia.

-- Data verification: Confirm that the data in the subpartition is dropped.
SELECT COUNT(*) FROM customer_data WHERE customer_id = 101;
-- The query result is 0.

    2. Update table statistics.

      After dropping a subpartition, immediately update the table's statistics to ensure the query optimizer generates accurate execution plans.

      ANALYZE CUSTOMER_DATA;

    FAQ

    Q1: Why do I get the ORA-00942: table or view does not exist error?

    A: This error typically occurs when you lack ALTER privileges on the target table or when the table or schema name is misspelled. Check your privilege settings and verify that the object names are correct.

    Q2: Why does the ORA-14006: invalid partition name error occur?
    A: This error indicates that the specified subpartition name does not exist or is misspelled. Query the ALL_TAB_SUBPARTITIONS view to verify the correct name. If the name is not enclosed in double quotation marks, the database converts it to uppercase for matching.

    Q3: Why do I get the ORA-01031: insufficient privileges error?
    A: You do not have ALTER privileges on the target table. Contact your database administrator to grant the necessary privileges.

    Q4: Can data be recovered after running DROP SUBPARTITION?
    A: No. DROP SUBPARTITION is a permanent deletion that bypasses the Recycle Bin. The only way to recover the data is to restore it from a database backup created before the operation.

    Related SQL statements