DROP and TRUNCATE Oracle Partitions

1) These series of images discuss partitioned tables with a local index

2) ALTER TABLE DROP PARTITION Partition A

If you DROP partition A

3) The table partition A is dropped

4) Along with the corresponding local index

ALTER TABLE TRUNCATE PARTITION Partition A REUSE STORAGE

If you TRUNCATE partition A.

6) The data in the partition is removed, but the partition remains

7) The corresponding local index partition is also truncated and marked as usable

Truncating Partitions

Use the ALTER TABLE ... TRUNCATE PARTITION statement to remove all rows from a table partition. Truncating a partition is similar to dropping a partition, except that the partition is emptied of its data, but not physically dropped.You cannot truncate an index partition. However, if local indexes are defined for the table, the

ALTER TABLE ... TRUNCATE PARTITION

statement truncates the matching partition in each local index. Unless you specify UPDATE INDEXES, any global indexes are marked UNUSABLE and must be rebuilt.

Truncating Segments That Are Empty

You can drop empty segments in tables and table fragments with the DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS procedure. In addition, if a partition or subpartition has a segment, then the truncate feature drops the segment if the DROP ALL STORAGE clause is specified with the ALTER TABLE TRUNCATE PARTITION SQL statement.