These options also apply to any associated indexes. When a table or cluster is truncated, all associated indexes are also truncated. The storage parameters for a truncated table, cluster, or associated indexes are not changed as a result of the truncation.
These TRUNCATE options are:
DROP STORAGE, the default option, reduces the number of extents allocated to the resulting table to the original setting for MINEXTENTS. Freed extents are then returned to the system and can be used by other objects.
DROP ALL STORAGE drops the segment. In addition to the TRUNCATE TABLE statement, DROP ALL STORAGE also applies to the ALTER TABLE TRUNCATE(SUB)PARTITION statement. This option also drops any dependent object segments associated with the partition being truncated.
DROP ALL STORAGE is not supported for clusters.
Note:
- This functionality is available with Oracle Database 11g release 2 (11.2.0.2). TRUNCATE TABLE emp DROP ALL STORAGE;
REUSE STORAGE specifies that all space currently allocated for the table or cluster remains allocated to it. For example, the following statement truncates the emp_dept cluster, leaving all extents previously allocated for the cluster available for subsequent inserts and deletes:
TRUNCATE CLUSTER emp_dept REUSE STORAGE;