If a partition contains data and has referential integrity constraints, then you cannot truncate the partition. If no other data is referencing any data in the partition to remove, then choose either of the following methods (method 1 or 2) to truncate the table partition.
Disable the integrity constraints, run the
PARTITION statement, then re-enable the integrity constraints. This method is most appropriate for large tables where the partition being truncated contains a significant percentage of the total data in the table. If there is still referencing data in other tables, then you must remove that data to be able to re-enable the integrity constraints.
DELETE statement to delete all rows from the partition before you issue the
PARTITION statement. The
DELETE statement enforces referential integrity constraints, and also fires triggers and generates redo and undo logs. Data in referencing tables is deleted if the foreign key constraints were created with the
ON DELETE CASCADE option.
DELETE FROM sales partition (dec94); ALTER TABLE sales TRUNCATE PARTITION dec94;
This method is most appropriate for small tables, or for large tables when the partition being truncated contains a small percentage of the total data in the table.