TRUNCATE TABLE ... DROP ALL STORAGE

This post looks at changes to the TRUNCATE statement between Oracle 11.2.0.1 and 11.2.0.2. I tested the first part on Oracle 11.2.0.1.0. I created a table and counted its extents: 

SQL> create table andrew

  2  storage (initial 8k next 8k)

  3  as select * from dba_tables

  4  /

 

Table created.

 

SQL> select count(*) from user_extents

  2  where segment_name = 'ANDREW'

  3  /

 

  COUNT(*)

----------

        15

 

SQL>

Then I truncated it and showed that it was left with only 1 extent. I think I have demonstrated this already in another post: 

SQL> truncate table andrew drop storage

  2  /

 

Table truncated.

 

SQL> select count(*) from user_extents

  2  where segment_name = 'ANDREW'

  3  /

 

  COUNT(*)

----------

         1

 

SQL>

Then I checked that the DROP ALL STORAGE option was not available: 

SQL> truncate table andrew drop all storage

  2  /

truncate table andrew drop all storage

                           *

ERROR at line 1:

ORA-03291: Invalid truncate option - missing STORAGE

keyword

 

SQL>

I tested the next part on Oracle 11.2.0.2.7. First I created a table as before. This time the number of extents was not so important: 

SQL> create table fred

  2  as select * from dba_tables

  3  /

 

Table created.

 

SQL>

Then I tried out the new TRUNCATE TABLE ... DROP ALL STORAGE command, which apparently first appeared in Oracle 11.2.0.2: 

SQL> truncate table fred drop all storage

  2  /

 

Table truncated.

 

SQL>

This removed ALL the table’s extents, left an entry for it in USER_TABLES but removed its entry from USER_SEGMENTS: 

SQL> select count(*) from user_extents

  2  where segment_name = 'FRED'

  3  /

 

  COUNT(*)

----------

         0

 

SQL> select count(*) from user_tables

  2  where table_name = 'FRED'

  3  /

 

  COUNT(*)

----------

         1

 

SQL> select count(*) from user_segments

  2  where segment_name = 'FRED'

  3  /

 

  COUNT(*)

----------

         0

 

SQL>