11gR2: Truncate table does deallocate all extents?

In Oracle 11gR2 11.2.0.2 there is a new clause added to the truncate table SQL statement allowing to release the space allocated even for extents corresponding to the minextents storage clause. Prior to 11.2.0.2 truncate table statement with its different flavor of clauses was used to delete all the rows from the table and possibly release the space but up to the space allocated with minextents.

STORAGE Clauses

The STORAGE clauses let you determine what happens to the space freed by the truncated rows. The DROP STORAGE clause, DROP ALL STORAGE clause, and REUSE STORAGE clause also apply to the space freed by the data deleted from associated indexes.

DROP STORAGE Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the MINEXTENTS parameter of the table. This space can subsequently be used by other objects in the tablespace. Oracle Database also sets the NEXT storage parameter to the size of the last extent removed from the segment in the truncation process. This setting, which is the default, is useful for small and medium-sized objects. The extent management in locally managed tablespace is very fast in these cases, so there is no need to reserve space.

SQL> Truncate table drop storage;

DROP ALL STORAGE Specify DROP ALL STORAGE to deallocate all space from the deleted rows from the table, including the space allocated by the MINEXTENTS parameter. All segments for the table, as well as all segments for its dependent objects, will be deallocated.

SQL> Truncate table drop all storage;

REUSE STORAGE Specify REUSE STORAGE to retain the space from the deleted rows allocated to the table. Storage values are not reset to the values when the table was created. This space can subsequently be used only by new data in the table resulting from insert or update operations. This clause leaves storage parameters at their current settings

SQL> Truncate table reuse storage;

Example: Test case:-

This option will only works in conjunction with other new feature called deferred_segment_creation.

SQL> show parameter defer
NAME                                 TYPE        VALUE
-------------------------------------------------------
deferred_segment_creation            boolean     TRUE

SQL> create table mytest01 (col number) storage
(initial 60k next 60k minextents 4 maxextents unlimited pctincrease 0);

Table created.

SQL> select segment_name, segment_type, extent_id,  bytes,
blocks  from user_extents where segment_name = 'MYTEST01';

no rows selected

SQL> select segment_name, segment_type, extent_id,  bytes,
blocks  from user_extents where segment_name = 'MYTEST01';

SQL> insert into mytest01 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> truncate table mytest01 drop storage;      

Table truncated.

SQL> select segment_name, segment_type, extent_id,  bytes, blocks 
from user_extents where segment_name = 'MYTEST01';

SEGMENT_NAME  SEGMENT_TYPE        EXTENT_ID      BYTES     BLOCKS
------------- ------------------ ---------- ---------- ----------
MYTEST01      TABLE                       0      65536          8
MYTEST01      TABLE                       1      65536          8
MYTEST01      TABLE                       2      65536          8
MYTEST01      TABLE                       3      65536          8

SQL> insert into mytest01 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL>

SQL> insert into mytest01 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> truncate table mytest01 reuse storage;

Table truncated.

SQL> select segment_name, segment_type, extent_id,  bytes,
blocks  from user_extents where segment_name = 'MYTEST01';

SEGMENT_NAME   SEGMENT_TYPE        EXTENT_ID      BYTES     BLOCKS
-------------- ------------------ ---------- ---------- ----------
MYTEST01       TABLE                       0      65536          8
MYTEST01       TABLE                       1      65536          8
MYTEST01       TABLE                       2      65536          8
MYTEST01       TABLE                       3      65536          8

SQL> truncate table mytest01 drop all storage;

Table truncated.

SQL> select segment_name, segment_type, extent_id,  bytes,
blocks  from user_extents where segment_name = 'MYTEST01';

no rows selected.

See the extents dropped with drop all storage clause, this is nice feature to bring down the high water mark of table to the min extents or completely remove.
But this can work only you have segment deferred creation enabled in database, ofcourse it has some caevets especially with impdp/expdp

-Thanks
Geek DBA