Getting free space ORA errors even though tablespace has enough free space

Introduction

Recently I've seen an interesting question in the OTN discussions forums. 

The question was - Why do I get free space errors (such as ORA-01654) even though tablespace has enough free space. Here is the link to the thread: https://community.oracle.com/message/13869346#13869346

While this may sounds like a weird and complex scenario -  it is actually a pretty common issue and simple to explain.

Background

When Oracle needs to allocate more space for a segment (such as index segment, table segment, etc.) it doesn't allocate a single Oracle Block for the extra space, but rather an extent which is a logical unit of database storage space allocation made up of a number of contiguous data blocks.

When there is no more enough free space in the database blocks, and Oracle needs to allocate, it has to allocate an extent. The size of the extent depends on the tablespace storage configuration which could have either uniform extents sizes where each new extent has the same size, or system-managed extents sizes where Oracle determines the optimal size of additional extents and the extent sizes may vary (Read more about this here: http://docs.oracle.com/cd/B28359_01/server.111/b28318/logical.htm#i19599)

Solving The Mystery

The total free space in a tablespace might be large enough for allocating additional extent; however, in some cases where the tablespace is fragmented, the largest contiguous extent might be smaller than the next extent size. 

In order to obtain the next extent size, it is possible to query the NEXT column in the DBA_SEGMENTS dictionary view, as follows:

SQL> CREATE TABLE large_extent_tbl
(
   id   NUMBER (20)
)
STORAGE (NEXT 10M)
TABLESPACE TEST;  
Table created.

SQL> insert into large_extent_tbl values (1);

1 row created.

SQL> SELECT next_extent/1024/1024
  FROM dba_segments
 WHERE segment_name = 'LARGE_EXTENT_TBL';

NEXT_EXTENT/1024/1024
---------------------
                   10

As you can see I created the table with a NEXT EXTENT definition of 10 which is larger than the largest contiguous extent in the tablespace. In order to obtain the largest contiguous extent in the tablespace we can query DBA_FREE_SPACE as follows:

SQL> SELECT ROUND(SUM (bytes) / 1024 / 1024) "total_free_space (MB)",
       ROUND(MAX (bytes) / 1024 / 1024) "largest_contiguous_extent (MB)"
  FROM dba_free_space
 WHERE tablespace_name = 'TEST' ; 
total_free_space (MB) largest_contiguous_extent (MB)
--------------------- ------------------------------
                  128                              6

SQL>

As you can see, the largest contiguous extent is 6MB which is smaller than the NEXT extent for the segment (which is 10MB).

Additional Resources

• AskTom - How to Find tablespace Fragmentation
• MOS DOC ID 162994.1 - Script to Report Extents and Contiguous Free Space