Friday, October 19, 2012

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>

No comments: