Understanding REUSE STORAGE

February 25, 2025

3–4 minutes

I had a question from a customer recently about TRUNCATE versus TRUNCATE REUSE STORAGE. They pointed out that after a TRUNCATE REUSE STORAGE, the number of blocks for table dropped to zero which they were not expecting.

Lets explain why zero blocks is expected, and does not imply that we ignored the REUSE STORAGE command.

I’ll start with a tablespace that is large enough to hold one copy of DBA_OBJECTS but not large enough to hold two copies of it.


SQL> create tablespace JUST_ENOUGH_SPACE
  2  datafile 'X:\ORADATA\DB21\PDB21A\JUST_ENOUGH_SPACE.DBF' size 25m
  3  extent management local
  4  uniform size 1m;

Tablespace created.

Now I’ll create my first table, which will consume ~16MB of the available 25.


SQL> create table t tablespace just_enough_space
  2  as select * from dba_objects;

Table created.

SQL>
SQL> select blocks*8192 bytes
  2  from user_tables
  3  where table_name = 'T';

     BYTES
----------
  15900672

SQL>
SQL> select bytes
  2  from user_segments
  3  where segment_name = 'T';

     BYTES
----------
  16777216

SQL>
SQL> select count(*)
  2  from user_extents
  3  where segment_name = 'T';

  COUNT(*)
----------
        16

It doesn’t take much deductive skills to guess what will happen when I try create a second copy of the table in the same tablespace.


SQL> create table t1 tablespace just_enough_space as
  2  select * from dba_objects;
create table t1 tablespace just_enough_space as
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace JUST_ENOUGH_SPACE

Now first let me prove that a standard TRUNCATE does indeed release all of the storage. I’ll truncate my table, and check what space utilisation I have


SQL> truncate table t;

Table truncated.

SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks*8192 bytes
  2  from user_tables
  3  where table_name = 'T';

     BYTES
----------
         0

SQL>
SQL> select bytes
  2  from user_segments
  3  where segment_name = 'T';

     BYTES
----------
   1048576

SQL>
SQL> select count(*)
  2  from user_extents
  3  where segment_name = 'T';

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

As a result, I can now fit my second table T1 in the tablespace, because I’ve got 24MB of free space.


SQL> create table t1 tablespace just_enough_space as
  2  select * from dba_objects;

Table created.

But it is also worth noting the difference between how we report table blocks in use versus space allocation in the tablespace. Our empty table has zero blocks used, but is occupying 1MB (a single extent) in the tablespace. That is a critical difference, as we’ll now discover when it comes to REUSE STORAGE.

I’ll drop both tables to leave us once again with an empty tablespace, and recreate my table T with its 16MB of data.


SQL> drop table t purge;

Table dropped.

SQL> drop table t1 purge;

Table dropped.

SQL> create table t tablespace just_enough_space
  2  as select * from dba_objects;

Table created.

SQL>
SQL> select blocks*8192 bytes
  2  from user_tables
  3  where table_name = 'T';

     BYTES
----------
  15900672

This time I will truncate table T with the REUSE STORAGE clause, and we’ll see where the confusion can come in.


SQL> truncate table t reuse storage;

Table truncated.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks*8192 bytes
  2  from user_tables
  3  where table_name = 'T';

     BYTES
----------
         0

The data dictionary is telling me that there are zero blocks used by this table. This is indeed correct. There are no blocks with any data in table T. But blocks containing data is not the same as storage consumption. If we look at storage consumption in the tablespace, we can finally see how RESUSE STORAGE works.


SQL> select bytes
  2  from user_segments
  3  where segment_name = 'T';

     BYTES
----------
  16777216

SQL>
SQL> select count(*)
  2  from user_extents
  3  where segment_name = 'T';

  COUNT(*)
----------
        16

Even though the table is empty, it still occupies the original storage it had when it was full before the truncate.

As a consequence, we can still not fit two copies in the tablespace.


SQL> create table t1 tablespace just_enough_space as
  2  select * from dba_objects;
create table t1 tablespace just_enough_space as
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace JUST_ENOUGH_SPACE

3 responses to “Understanding REUSE STORAGE”

pleasantlyenthusiast10209b8377

February 25, 2025 at 5:06 pm
Good morning Connor,
just a thought about the differences in computing the column “blocks” in USER_TABLES than in the effective extents size computation of the segment representing the table.

At this point the column value of DBA/ALL/USER_TABLES.BLOCKS has, by purpose or as expected behavior, a different meaning than (and to have the effective value of this column, table has been submitted to immediate statistics calculation as per your example), the column value we have in DBA/ALL/USER_SEGMENTS.BLOCKS (or in XXX_EXTENTS as well).

I’m not surprised about the fact that if you truncate a table (without using REUSE STORAGE), if you check the allocated space in terms of “segment”, in USER_SEGMENTS and USER_EXTENTS you find something like:

SQL> select bytes

2 from user_segments

3 where segment_name = ‘T’;
```
  BYTES
```
Reply
1. Connor McDonald
  
  February 25, 2025 at 5:46 pm
  
  I think this comment got truncated (no pun intended) by wordpress, but just on the: ” the differences in computing the column “blocks” in USER_TABLES than in the effective extents size computation”.
  
  That is expected, eg
  – I create a table as select 1 from dual
  – That table probably needs 1 block of data (plus some metadata – segment header, bitmap blocks etc).
  
  So USER_TABLES.BLOCKS will read 4 blocks, but USER_EXTENTS will read 128 blocks (ie, 1 extent in my case). For a typical tablespace (auto extent size) it would be 8 blocks.
  
  So the last extent will typically be partially full, which means a discrepancy
  
  Reply
iudithd5bf8e4d8d

February 25, 2025 at 10:41 pm

Hello All,

Maybe what the previous poster is talking about is the fact that, when simply specifying TRUNCATE TABLE T, the default behavior is
DROP STORAGE, as opposed to DROP ALL STORAGE ( this last option was added in version 11 R2, as by the documentation ).

The meaning of DROP STORAGE is to keep allocated a space as defined by the MINEXTENTS of the table
( which in Connor’s example was defaulted to 1 extent of 1M ).

Using DROP ALL STORAGE will deallocate all the extents.

Cheers & Best Regards,
Iudith Mentzel

Reply