Issue

During Tibero operation, the following error frequently occurs in sys.log.

ec=ERROR_TX_CANT_ALLOC_EXT(-21004)
No more extent available in tablespace 'TIBERO_TS'.

This error occurs when an extent cannot be allocated in the tablespace.

Cause

This error occurs not only when there is simply insufficient physical space, but also when there is enough space but it is not in a usable form.

1. Insufficient Tablespace Capacity (Normal Case)

This is when the total capacity of the tablespace is insufficient to allocate an extent.

2. Data File Fragmentation

This occurs when the free areas inside the tablespace are fragmented, resulting in a lack of contiguous space.

If continuous table creation and deletion occur, the free space is distributed as follows:

• [In Use] [FREE] [In Use] [FREE] [In Use] [In Use] [FREE]

In this case, although the total free space is sufficient, the lack of a large contiguous space causes extent allocation to fail.

In Tibero, as segments grow larger, increasingly larger extents are allocated. 

In other words, as the segment grows, Tibero tries to allocate larger extents, requiring larger contiguous space.

If tablespace fragmentation progresses, although the total free space size is sufficient, the lack of large contiguous space can cause extent allocation to fail.

1. Request the largest appropriate extent size
2. If failed, retry with half the size (retry down to the minimum extent)
3. If no free blocks are available at the last minimum extent allocation, allocation fails and an error occurs

SQL to Check TABLESPACE Fragmentation

WITH free AS (
    SELECT blocks
    FROM dba_free_space
    WHERE tablespace_name = 'USR'
)
SELECT
    SUM(CASE WHEN blocks >= 8192 THEN 1 END) AS cnt_8192,
    ROUND(SUM(CASE WHEN blocks >= 8192 THEN floor(blocks/8192)*8192*8/1024/1024 END),2) AS mb_8192,

    SUM(CASE WHEN blocks >= 4096 THEN 1 END) AS cnt_4096,
    ROUND(SUM(CASE WHEN blocks >= 4096 THEN floor(blocks/4096)*4096*8/1024/1024 END),2) AS mb_4096,

    SUM(CASE WHEN blocks >= 2048 THEN 1 END) AS cnt_2048,
    ROUND(SUM(CASE WHEN blocks >= 2048 THEN floor(blocks/2048)*2048*8/1024/1024 END),2) AS mb_2048,

    SUM(CASE WHEN blocks >= 1024 THEN 1 END) AS cnt_1024,
    ROUND(SUM(CASE WHEN blocks >= 1024 THEN floor(blocks/1024)*1024*8/1024/1024 END),2) AS mb_1024,

    SUM(CASE WHEN blocks >= 512 THEN 1 END) AS cnt_512,
    ROUND(SUM(CASE WHEN blocks >= 512 THEN floor(blocks/512)*512*8/1024/1024 END),2) AS mb_512,

    SUM(CASE WHEN blocks >= 256 THEN 1 END) AS cnt_256,
    ROUND(SUM(CASE WHEN blocks >= 256 THEN floor(blocks/256)*256*8/1024/1024 END),2) AS mb_256,

    SUM(CASE WHEN blocks >= 16 THEN 1 END) AS cnt_16,
    ROUND(SUM(CASE WHEN blocks >= 16 THEN floor(blocks/16)*16*8/1024/1024 END),2) AS mb_16
FROM free;



  CNT_8192    MB_8192   CNT_4096    MB_4096   CNT_2048    MB_2048   CNT_1024    MB_1024    CNT_512     MB_512    CNT_256     MB_256     CNT_16      MB_16
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1       1.69          1       1.72          1       1.73          1       1.74          1       1.75          1       1.75          1       1.75

• CNT_8192

  Number of continuous free extents of 8192 blocks (= about 64MB).

  If the value is 0 → no continuous free space of 64MB size → large segment expansion not possible

   

• MB_8192

  Total free space available in units of 8192 blocks (converted to MB).

  (Example: If 5 → total usable free space combined in 64MB units is about 5MB)

The following can be determined from this query:

1. Is there a large contiguous free space in the current TABLESPACE?

2. Is there a possibility that the segment will fail to allocate the next extent?

3. Is there a lot of free space but fragmented, making TBR-21004 likely to occur?

4. Is the fragmentation state such that Table/Index MOVE is necessary?

Solutions

To address TBR-21004 (ERROR_TX_CANT_ALLOC_EXT), two methods are available.

Add Data Files / Increase Size of Existing Data Files

# Add Data File
ALTER TABLESPACE USR ADD DATAFILE '/path/usr02.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE 30G;


# Increase Size of Existing Data File

ALTER DATABASE DATAFILE '/path/usr01.dbf' RESIZE 30G;

Use when internal table fragmentation is severe (Table Move + Index Rebuild)

# Table Move
ALTER TABLE table_name MOVE TABLESPACE USR;

# Index Rebuild


alter system set _INDEX_BUILD_USING_FULL_SCAN=Y;
ALTER INDEX index_name REBUILD;
alter system set _INDEX_BUILD_USING_FULL_SCAN=N;

Note

The SQL below can be used to diagnose fragmentation in the USR Tablespace.

SET SERVEROUTPUT ON
DECLARE
   -- Variables to store results
   cnt_8192 NUMBER; mb_8192 NUMBER;
   cnt_4096 NUMBER; mb_4096 NUMBER;
   cnt_2048 NUMBER; mb_2048 NUMBER;
   cnt_1024 NUMBER; mb_1024 NUMBER;
   cnt_512  NUMBER; mb_512  NUMBER;
   cnt_256  NUMBER; mb_256  NUMBER;
   cnt_16   NUMBER; mb_16   NUMBER;

   v_msg VARCHAR2(4000);
BEGIN
   -- Summary query of Free Space
   WITH free AS (
       SELECT blocks
       FROM dba_free_space
       WHERE tablespace_name = 'USR'
   )
   SELECT
       SUM(CASE WHEN blocks >= 8192 THEN 1 END),
       ROUND(SUM(CASE WHEN blocks >= 8192 THEN floor(blocks/8192)*8192*8/1024/1024 END),2),

       SUM(CASE WHEN blocks >= 4096 THEN 1 END),
       ROUND(SUM(CASE WHEN blocks >= 4096 THEN floor(blocks/4096)*4096*8/1024/1024 END),2),

       SUM(CASE WHEN blocks >= 2048 THEN 1 END),
       ROUND(SUM(CASE WHEN blocks >= 2048 THEN floor(blocks/2048)*2048*8/1024/1024 END),2),

       SUM(CASE WHEN blocks >= 1024 THEN 1 END),
       ROUND(SUM(CASE WHEN blocks >= 1024 THEN floor(blocks/1024)*1024*8/1024/1024 END),2),

       SUM(CASE WHEN blocks >= 512 THEN 1 END),
       ROUND(SUM(CASE WHEN blocks >= 512 THEN floor(blocks/512)*512*8/1024/1024 END),2),

       SUM(CASE WHEN blocks >= 256 THEN 1 END),
       ROUND(SUM(CASE WHEN blocks >= 256 THEN floor(blocks/256)*256*8/1024/1024 END),2),

       SUM(CASE WHEN blocks >= 16 THEN 1 END),
       ROUND(SUM(CASE WHEN blocks >= 16 THEN floor(blocks/16)*16*8/1024/1024 END),2)
   INTO
       cnt_8192, mb_8192,
       cnt_4096, mb_4096,
       cnt_2048, mb_2048,
       cnt_1024, mb_1024,
       cnt_512 , mb_512 ,
       cnt_256 , mb_256 ,
       cnt_16  , mb_16
   FROM free;

   DBMS_OUTPUT.PUT_LINE('=== Tablespace Fragmentation Auto Analysis Result (USR) ===');
   DBMS_OUTPUT.PUT_LINE('');

   DBMS_OUTPUT.PUT_LINE('64MB(8192 block) contiguous space: ' || cnt_8192 || ' count, total ' || mb_8192 || ' MB');
   DBMS_OUTPUT.PUT_LINE('32MB(4096 block) contiguous space: ' || cnt_4096 || ' count, total ' || mb_4096 || ' MB');
   DBMS_OUTPUT.PUT_LINE('16MB(2048 block) contiguous space: ' || cnt_2048 || ' count, total ' || mb_2048 || ' MB');
   DBMS_OUTPUT.PUT_LINE('');

   -- Analysis Logic -----------------------------------------

   -- ① Determine largest 64MB unit
   IF cnt_8192 = 0 THEN
       v_msg := v_msg || '* No continuous free space of 64MB or more → High risk of large segment expansion)' || chr(10);
   ELSE
       v_msg := v_msg || '* Continuous free space of 64MB or more exists → Large segment expansion possible' || chr(10);
   END IF;

   -- ② 32MB unit
   IF cnt_4096 = 0 THEN
       v_msg := v_msg || '* No continuous free space of 32MB → Risk when expanding medium-large segments' || chr(10);
   END IF;

   -- ③ Free space is abundant but fragmented
   IF cnt_8192 = 0 AND cnt_4096 = 0 AND cnt_2048 > 0 THEN
       v_msg := v_msg || '* Free space exists but no large fragments, severe fragmentation state' || chr(10);
   END IF;

   -- ④ Actual data file shortage (for reference)
   IF mb_16 < 50 THEN
       v_msg := v_msg || '* Total free space less than 50MB → Possible actual space shortage' || chr(10);
   END IF;

   -- ⑤ Final risk assessment
   DBMS_OUTPUT.PUT_LINE('--- Comprehensive Fragmentation Diagnosis ---');
   IF cnt_8192 = 0 AND cnt_4096 = 0 THEN
       DBMS_OUTPUT.PUT_LINE('>> Risk Level: ★★★ (High)');
   ELSIF cnt_8192 = 0 AND cnt_4096 > 0 THEN
       DBMS_OUTPUT.PUT_LINE('>> Risk Level: ★★ (Medium)');
   ELSE
       DBMS_OUTPUT.PUT_LINE('>> Risk Level: ★ (Low)');
   END IF;

   DBMS_OUTPUT.PUT_LINE('');
   DBMS_OUTPUT.PUT_LINE(v_msg);

END;
/

[Execution Result]

64MB(8192 block) contiguous space: 1 count, total 1.69 MB
32MB(4096 block) contiguous space: 1 count, total 1.72 MB
16MB(2048 block) contiguous space: 1 count, total 1.73 MB

--- Comprehensive Fragmentation Diagnosis ---
>> Risk Level: ★ (Low)

* Continuous free space of 64MB or more exists → Large segment expansion possible
* Total free space less than 50MB → Possible actual space shortage