ALTER TABLE...TRUNCATE PARTITION v16

Use the ALTER TABLE… TRUNCATE PARTITION command to remove the data from the specified partition and leave the partition structure intact. The syntax is:

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

Parameters

table_name

The name (optionally schema-qualified) of the partitioned table.

partition_name

The name of the partition to delete.

Description

Use the ALTER TABLE… TRUNCATE PARTITION command to remove the data from the specified partition and leave the partition structure intact. Truncating a partition also truncates its subpartitions.

ALTER TABLE… TRUNCATE PARTITION doesn't cause ON DELETE triggers for the table to fire, but it fires ON TRUNCATE triggers. If an ON TRUNCATE trigger is defined for the partition, all BEFORE TRUNCATE triggers are fired before any truncation happens. All AFTER TRUNCATE triggers are fired after the last truncation occurs.

You must have the TRUNCATE privilege on a table to invoke ALTER TABLE… TRUNCATE PARTITION.

DROP STORAGE and REUSE STORAGE are included only for compatibility. The clauses are parsed and ignored.

example_emptying_a_partition