Guenadi N Jilevski's Oracle BLOG

New truncate table feature (DROP ALL STORAGE) in Oracle 11gR2 11.2.0.2

New truncate table feature (DROP ALL STORAGE) in Oracle 11gR2 11.2.0.2

Starting with 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. Now in 11.2.0.2 a new clause DROP ALL STORAGE for the truncate table SQL statement allows releasing even the space allocated to store the extents specified by minextents storage clause. However this clause is a subject to the same restrictions that apply to the deferred segment creation. More on the deferred segment creation can be found in a previous post here.

Let’s create a table and see the new truncate table SQL statement feature. Notice in the example below that due to the deferred segment creation an extent is allocated only after the insert as the parameter deferred_segment_creation is set to true. It is only until when DROP ALL STORAGE is used than all extents are de-allocated and the space is released.

SQL> show parameter defer

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

deferred_segment_creation            boolean     TRUE

SQL>

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> insert into mytest01 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL>

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>

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>

SQL> truncate table mytest01;

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>

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

SQL>

December 4, 2010 - Posted by gjilevski | oracle