How to reclaim entire space of an oracle database table with “Truncate Table” statement

Using the TRUNCATE statement provides a fast, efficient method for deleting all rows from a table or cluster. A TRUNCATE statement does not generate any undo information and it commits immediately. It is a DDL statement and cannot be rolled back. A TRUNCATE statement does not affect any structures associated with the table being truncated (constraints and triggers) or authorizations. A TRUNCATE statement also specifies whether space currently allocated for the table is returned to the containing tablespace after truncation.

In the previous release 11gR1 when a truncate was executed Oracle keeps the segment associated with a table. Oracle 11gR1 does provide the DROP STORAGE and REUSE STORAGE clauses but the DROP STORAGE clause only drops extents beyond the minimum extents. Starting with Oracle 11gR2 (11.2.0.2), a TRUNCATE statement can also specify the DROP ALL STORAGE clause to release the space currently allocated for a table to the containing tablespace.

For example:

SQL> create table TEST01 storage (MINEXTENTS 3)
     as select * from dba_source;

SQL> select extents
     from   user_segments
     where  segment_name='TEST01';

EXTENTS
-----------
67

The “DROP STORAGE” clause is used by default:

SQL> truncate table TEST01;

SQL> select extents
     from   user_segments
     where  segment_name='TEST01';

EXTENTS
----------
3

Same truncate with “DROP ALL STORAGE” deletes all extents:

SQL> truncate table TEST01 DROP ALL STORAGE;

SQL> select extents
     from   user_segments
     where  segment_name='TEST01';

no rows selected

The object does not have a segment because deferred segment creation is true. A new extent will be allocated when a new row is inserted on that particular table.

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.