All Products
Search

This Product

    PolarDB:ALTER TABLE…TRUNCATE PARTITION

    Document Center

    PolarDB:ALTER TABLE…TRUNCATE PARTITION

    Last Updated:Mar 30, 2026

    Removes all data from a specified partition while keeping the partition definition intact. This operation permanently deletes partition data. Back up any data you need before running this command, because data cannot be recovered after the operation.

    Synopsis

    ALTER TABLE table_name TRUNCATE PARTITION partition_name
  [{DROP|REUSE} STORAGE]

    Parameters

    Parameter Required Description Example
    table_name Yes The name of the partitioned table. sales_records
    partition_name Yes The name of the partition to truncate. p_2023_q1
    `{DROP REUSE} STORAGE` No Parsed for Oracle syntax compatibility but has no effect on storage. DROP STORAGE

    Usage notes

    • Run this command as the table owner or a user with the ALTER privilege on the table.

    • TRUNCATE PARTITION acquires a table-level AccessExclusiveLock, which blocks all Data Manipulation Language (DML) and most Data Definition Language (DDL) operations on the table. Run this command during off-peak hours and allow enough time for it to complete.

    • The operation is a metadata update combined with file deletion. It completes in seconds regardless of partition size—even for partitions with hundreds of millions of rows—with minimal I/O, CPU, and memory overhead.

    • If the table has ON TRUNCATE triggers, this operation fires them. Make sure you understand the business logic of those triggers before proceeding.

    • The operation invalidates table statistics, which can degrade query performance. Run ANALYZE on the parent table immediately after truncating a partition.

    • TRUNCATE PARTITION fails if the table has global indexes. A global index maintains uniqueness and access paths across all partitions, and the operation cannot clear the relevant index entries without compromising index integrity.

    Example

    This example clears a historical quarterly partition from a sales records table—a typical data lifecycle management task where you delete old data but keep the partition structure for future reuse.

    Set up the environment

    Create a range-partitioned table and insert sample data.

    -- Create a table partitioned by sale_date
CREATE TABLE sales_records (
    sale_id    INT NOT NULL,
    product_id INT NOT NULL,
    sale_date  DATE NOT NULL
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION p_2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD'))
);

-- Insert one row into each partition
INSERT INTO sales_records VALUES (1, 101, TO_DATE('2023-01-15', 'YYYY-MM-DD'));
INSERT INTO sales_records VALUES (2, 102, TO_DATE('2023-04-20', 'YYYY-MM-DD'));
COMMIT;

    Check the partition before truncating

    Confirm the partition exists and contains data before making any changes.

    -- Confirm the partition p_2023_q1 exists
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_RECORDS';

-- Confirm the partition contains data
SELECT COUNT(*) FROM sales_records PARTITION (p_2023_q1);
-- Expected result: 1
    Important

    Back up any data you need before proceeding. TRUNCATE PARTITION permanently deletes data and cannot be rolled back.

    Truncate the partition

    ALTER TABLE sales_records TRUNCATE PARTITION p_2023_q1;

    Verify and update statistics

    Confirm that the partition definition is still present and its data has been removed, then update table statistics.

    -- Confirm the partition definition still exists
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_RECORDS';
-- Expected result: both p_2023_q1 and p_2023_q2 are still listed

-- Confirm the data has been deleted
SELECT COUNT(*) FROM sales_records PARTITION (p_2023_q1);
-- Expected result: 0

-- Update statistics on the parent table
ANALYZE sales_records;

    FAQ

    What is the difference between `TRUNCATE PARTITION` and `DROP PARTITION`?

    TRUNCATE PARTITION deletes the data in a partition but keeps the partition definition. DROP PARTITION removes both the partition definition and its data. Use TRUNCATE PARTITION when you plan to reload data into the same partition later.

    Can data be recovered after `TRUNCATE PARTITION`?

    No. TRUNCATE PARTITION is a DDL operation that permanently deletes data. By default, it is not recorded in transaction logs and cannot be rolled back or recovered using standard flashback methods.

    Why do I get the error `RA-02149: specified partition does not exist`?

    The partition name does not exist or has a case mismatch. Verify the exact name with:

    SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_RECORDS';

    If the name contains lowercase letters or special characters, wrap it in double quotation marks.

    Why do I get the error `ORA-01031: insufficient privileges`?

    The current user does not have the ALTER privilege on the target table. Contact your database administrator to grant the required privilege.

    Why do I get the error `ERROR: cannot truncate partition of table because it has global indexes`?

    TRUNCATE PARTITION cannot remove data from a table that has global indexes, because a global index spans all partitions and the operation cannot safely clear only the affected index entries. Drop the global indexes before truncating the partition, or restructure the table to avoid global indexes.

    Related SQL statements