All Products
Search

This Product

    PolarDB:ALTER TABLE…TRUNCATE PARTITION

    Document Center

    PolarDB:ALTER TABLE…TRUNCATE PARTITION

    Last Updated:Oct 23, 2025

    Removes all data from a specified partition of a partitioned table. This operation permanently deletes the data but retains the partition structure. Before you run this command, ensure that the data is no longer needed or has been backed up.

    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

    This clause is parsed for Oracle syntax compatibility but is ignored and has no effect on storage.

    DROP STORAGE/

    REUSE STORAGE

    Notes

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

    • TRUNCATE PARTITION 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.

    • TRUNCATE PARTITION is a metadata-only operation combined with file deletion. It completes in seconds, even for partitions with hundreds of millions of rows, and has minimal I/O, CPU, and memory overhead.

    • If ON TRUNCATE triggers are defined on the table, this operation fires them. Ensure you understand the business logic of these triggers to prevent unintended side effects.

    Examples

    This example demonstrates how to truncate a historical quarterly partition from a sales records table. This is a common data lifecycle management task, where you delete old data but retain the partition structure to load new data for the same period later.

    Prepare the environment

    This step creates a table named sales_records that is range-partitioned by sales date. It also creates two partitions, p_2023_q1 and p_2023_q2, to store data for the first and second quarters, respectively.

    -- Create a partitioned table
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 data
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;

    Perform the pre-check

    Before you truncate the partition, confirm that the data is backed up. Then, check the structure and data of the target partition.

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

-- Check the partition structure to confirm that the partition p_2023_q1 exists.
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_RECORDS';

-- Check the partition data to confirm that the partition contains data.
SELECT COUNT(*) FROM sales_records PARTITION (p_2023_q1);

    Run the command

    Run the TRUNCATE PARTITION command to truncate the p_2023_q1 partition.

    ALTER TABLE sales_records TRUNCATE PARTITION p_2023_q1;

    Verify the result

    Verify that the partition structure is retained and the data is deleted.

    -- Structure verification: Confirm that the partition definition still exists.
SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = 'SALES_RECORDS';
-- The query result still contains p_2023_q1.

-- Data verification: Confirm that the data in the partition has been deleted.
SELECT COUNT(*) FROM sales_records PARTITION (p_2023_q1);
-- The query result is 0.

    The truncate operation invalidates table statistics, which can degrade query performance. Run ANALYZE immediately after the operation.

    ANALYZE sales; -- Update the statistics information of the parent table.

    FAQ

    Q1: What is the difference between TRUNCATE PARTITION and DROP PARTITION?
    TRUNCATE PARTITION removes only the data within a partition but retains the partition definition. In contrast, DROP PARTITION drops both the partition definition and all its data.

    Q2: Can data be recovered after running TRUNCATE PARTITION?
    No. TRUNCATE 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.

    Q3: Why do I get the error RA-02149: specified partition does not exist when I run this command?
    This error indicates that the specified partition does not exist or that its name has a case mismatch. Verify the exact partition name by running the query in the pre-check section, or use double quotation marks for an exact match.

    Q4: Why do I get the error ORA-01031: insufficient privileges when I run this command?
    This error occurs because the user running the command does not have the ALTER privilege on the target table. Contact your database administrator to grant the required privilege.

    Q5: Why do I receive the error ERROR: cannot truncate partition of table because it has global indexes when I run this command?

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

    Related SQL statements