Truncate (Part 1)

This post shows the effect of the TRUNCATE statement. First create a table:

SQL> create table truncate_example
  2  as select * from dba_tables
  3  /

Table created.

SQL>

Count the lines in the table:

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
      3023

SQL>

And the number of extents:

SQL> select count(*) from dba_extents
  2  where segment_name = 'TRUNCATE_EXAMPLE'
  3  /

  COUNT(*)
----------
        14

SQL>

Truncate the table with the REUSE STORAGE option:

SQL> truncate table truncate_example reuse storage
  2  /

Table truncated.

SQL>

Now count the number of rows in the table. There will be none:

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
         0

SQL>

Truncate does not produce any undo information so if you do a rollback, the deleted lines will not reappear:

SQL> rollback;

Rollback complete.

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
         0

SQL>

Now count the number of extents. It will be the same as before because you included the REUSE STORAGE clause:

SQL> select count(*) from dba_extents
  2  where segment_name = 'TRUNCATE_EXAMPLE'
  3  /

  COUNT(*)
----------
        14

SQL>

Now reinsert the deleted rows from the source table:

SQL> insert into truncate_example
  2  select * from dba_tables
  3  /

3024 rows created.

SQL>

Truncate the table again but this time, add the DROP STORAGE clause, which is the default:

SQL> truncate table truncate_example
  2  drop storage
  3  /

Table truncated.

SQL>

Count the number of rows in the table again. There will be none, as before:

SQL> select count(*) from truncate_example
  2  /

  COUNT(*)
----------
         0

SQL>

And count the number of extents. By adding the DROP STORAGE clause, the table’s extents have been deallocated. The number of extents remaining is determined by the table’s MINEXTENTS value:

SQL> select count(*) from dba_extents
  2  where segment_name = 'TRUNCATE_EXAMPLE'
  3  /

  COUNT(*)
----------
         1

SQL>