Thursday, March 10, 2011

Truncate (Part 2)



This post illustrates a couple of points made in part 1. First, create a table and remember the MINEXTENTS value:

SQL> create table truncate_test
  2  storage(initial 8k next 8k minextents 3)
  3  as select * from dba_tables
  4  /

Table created.

SQL>
  
Check that the number of extents is greater than the MINEXTENTS value so that we can see the effect of the TRUNCATE statement:

SQL> select count(*) from user_extents
  2  where segment_name = 'TRUNCATE_TEST'
  3  /

  COUNT(*)
----------
         9

SQL>
  
TRUNCATE the table but do not specify either the DROP STORAGE or REUSE STORAGE clauses:

SQL> truncate table truncate_test
  2  /

Table truncated.

SQL>
  
Count the number of extents again. This shows that extents have been deallocated as if the DROP STORAGE clause had been specified, proving that this is the default. Note that the number of extents remaining is determined by the table's MINEXTENTS value:

SQL> select count(*) from user_extents
  2  where segment_name = 'TRUNCATE_TEST'
  3  /

  COUNT(*)
----------
         3

SQL>

No comments: