ALTER TABLE...TRUNCATE SUBPARTITION v16

Use the ALTER TABLE… TRUNCATE SUBPARTITION command to remove all of the data from the specified subpartition and leave the subpartition structure intact. The syntax is:

ALTER TABLE <table_name>
  TRUNCATE SUBPARTITION <subpartition_name>
  [{DROP|REUSE} STORAGE]

Parameters

table_name

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

subpartition_name

The name of the subpartition to truncate.

Description

The ALTER TABLE… TRUNCATE SUBPARTITION command removes all data from a specified subpartition, leaving the subpartition structure intact.

ALTER TABLE… TRUNCATE SUBPARTITION 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 subpartition, 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 SUBPARTITION.

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

example_emptying_a_subpartition