Oracle APEX 26 – How to Test & Fix Tablespace Storage Limits

Oracle APEX 26 – How to Test & Fix Tablespace Storage Limits

Blog: apexnote.de
Stack: Oracle DB 26ai EE · Oracle APEX 26.1 · Docker · ORDS 26.1

---

Introduction

We recently installed Oracle APEX 26.1 on Oracle Database 26ai Enterprise Edition running in Docker on a VPS server. As part of our setup, we wanted to run a storage test to verify that Oracle EE truly has no storage limits — and to document what happens when a tablespace runs out of space.

If you have installed Oracle APEX 26 yourself and run into the ORA-01691 error, or if you simply want to test your own database storage, this guide is for you.

---

Step 1 – Create the Test Table

Open Oracle APEX → SQL Workshop → SQL Commands and create a simple table with a CLOB column:

CREATE TABLE test_storage_text (
    id          NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    titel       VARCHAR2(500),
    inhalt      CLOB,
    created_at  TIMESTAMP DEFAULT SYSTIMESTAMP
);

---

Step 2 – Insert a Large CLOB Record (~19 MB)

We generate a single row with approximately 19 MB of text data using DBMS_LOB. This gives us a solid base row to duplicate later:

DECLARE
    v_clob CLOB;
    v_text VARCHAR2(32000) := 'Oracle Database 26ai Enterprise Edition offers unlimited storage 
and highest performance for enterprise-critical applications. Oracle APEX 26.1 enables 
rapid application development directly inside the database. This is a test text to fill 
the CLOB field with a large amount of content. ';
BEGIN
    DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);

    -- Repeat the text 10,000 times to create ~19 MB
    FOR i IN 1..10000 LOOP
        DBMS_LOB.APPEND(v_clob, v_text);
    END LOOP;

    INSERT INTO test_storage_text (titel, inhalt)
    VALUES ('Large Text Record', v_clob);

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Done! Size: ' || ROUND(DBMS_LOB.GETLENGTH(v_clob)/1024/1024, 2) || ' MB');
END;

---

Step 3 – Check Storage Size

Oracle stores CLOB data automatically in a separate LOB Segment. To see the real storage usage of your table run this query:

SELECT 
    segment_name,
    segment_type,
    ROUND(bytes/1024/1024, 2) AS "MB"
FROM user_segments
WHERE segment_name = 'TEST_STORAGE_TEXT'
   OR segment_name IN (
       SELECT segment_name FROM user_lobs 
       WHERE table_name = 'TEST_STORAGE_TEXT'
   )
ORDER BY bytes DESC;

Example result:

SEGMENT_NAME	SEGMENT_TYPE	MB
SYS_LOB0000...$$	LOBSEGMENT	19.25
TEST_STORAGE_TEXT	TABLE	0.06

Important: The LOB Segment holds your actual data. Both rows together represent the real table size. This is standard Oracle behavior — not an error.

---

Step 4 – Duplicate Rows to Fill Storage

Instead of generating new random data (which is slow), we duplicate the existing row using a loop. Each iteration commits individually to prevent data loss on connection timeout:

BEGIN
    FOR i IN 1..100 LOOP
        INSERT INTO test_storage_text (titel, inhalt)
        SELECT 'copy_' || i, inhalt
        FROM test_storage_text
        WHERE id = 1;
        COMMIT;
    END LOOP;
END;

To grow even faster, duplicate all existing rows at once:

INSERT /*+ APPEND */
INTO test_storage_text (titel, inhalt)
SELECT 'dup_' || ROWNUM, inhalt FROM test_storage_text;
COMMIT;

Run this last statement multiple times and watch exponential growth:

Run	Size
Start	~19 MB
After 100x loop	~1.9 GB
Doubling x1	~3.8 GB
Doubling x2	~7.6 GB
Doubling x3	~15 GB
Doubling x4	~30 GB
Doubling x5	~60 GB
Doubling x6	~120 GB ✅

---

Step 5 – The Error: ORA-01691

At some point the tablespace datafile reaches its maximum size (default 32 GB) and Oracle throws:

ORA-01691: unable to extend lob segment SAJJAD.SYS_LOB0000078881C00003$$
           by 1024 in tablespace APEX_1500859741478879
ORA-06512: at line 3

This means the datafile is full and AUTOEXTEND cannot grow further because MAXSIZE equals the current file size.

---

Step 6 – The Fix: Tablespace Monitor & AutoExtend Script

We built a reusable PL/SQL script that:

• Shows a full tablespace and datafile overview
• Detects if a datafile is full (≥ 32 GB)
• Automatically calculates the next datafile name (_02, _03, _04 ...)
• Adds a new datafile with MAXSIZE UNLIMITED
• Has a dry run mode — set to YES only when you are ready

SET SERVEROUTPUT ON;

-- ============================================================
-- Oracle Tablespace & Datafile Monitor with AutoExtend Control
-- Automatically detects next free datafile number
-- Author: Sajjad | Blog: https://apexnote.de
-- ============================================================

DECLARE
    v_autoextend_enable  VARCHAR2(3) := 'NO'; -- << Set to YES or NO
    v_new_file           VARCHAR2(500);
    v_base_file          VARCHAR2(500);
    v_file_count         NUMBER;
    v_next_num           VARCHAR2(10);
    v_dir                VARCHAR2(500);
    v_filename           VARCHAR2(200);
    v_dot_pos            NUMBER;
    v_slash_pos          NUMBER;
BEGIN
    -- ===== TABLESPACE OVERVIEW =====
    DBMS_OUTPUT.PUT_LINE('===== TABLESPACE OVERVIEW =====');
    FOR r IN (
        SELECT dt.tablespace_name,
               SUM(df.bytes)/1024/1024 AS total_mb,
               NVL(SUM(fs.bytes),0)/1024/1024 AS free_mb
        FROM dba_tablespaces dt
        JOIN dba_data_files df ON dt.tablespace_name = df.tablespace_name
        LEFT JOIN dba_free_space fs ON dt.tablespace_name = fs.tablespace_name
        WHERE dt.tablespace_name LIKE 'APEX%'
        GROUP BY dt.tablespace_name
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('TS: ' || r.tablespace_name);
        DBMS_OUTPUT.PUT_LINE('  Total:  ' || ROUND(r.total_mb,2) || ' MB');
        DBMS_OUTPUT.PUT_LINE('  Free:   ' || ROUND(r.free_mb,2)  || ' MB');
        DBMS_OUTPUT.PUT_LINE('  Used:   ' || ROUND(r.total_mb - r.free_mb,2) || ' MB');
        DBMS_OUTPUT.PUT_LINE('---');
    END LOOP;

    -- ===== DATAFILE DETAILS =====
    DBMS_OUTPUT.PUT_LINE('===== DATAFILE DETAILS =====');
    FOR f IN (
        SELECT file_name, bytes/1024/1024 AS size_mb,
               autoextensible, maxbytes/1024/1024 AS max_mb,
               tablespace_name
        FROM dba_data_files
        WHERE tablespace_name LIKE 'APEX%'
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('File: ' || f.file_name);
        DBMS_OUTPUT.PUT_LINE('  Size:       ' || ROUND(f.size_mb,2) || ' MB');
        DBMS_OUTPUT.PUT_LINE('  AutoExtend: ' || f.autoextensible);
        DBMS_OUTPUT.PUT_LINE('  MaxSize:    ' || ROUND(f.max_mb,2) || ' MB');

        IF f.size_mb >= f.max_mb OR f.size_mb >= 32000 THEN
            DBMS_OUTPUT.PUT_LINE('  !! DATAFILE IS FULL - new datafile needed !!');

            -- Count existing datafiles to calculate next number
            SELECT COUNT(*) INTO v_file_count
            FROM dba_data_files
            WHERE tablespace_name = f.tablespace_name;

            -- Format next number: 2 -> "02", 10 -> "10"
            v_next_num := LPAD(TO_CHAR(v_file_count + 1), 2, '0');

            -- Extract directory path
            v_slash_pos := INSTR(f.file_name, '/', -1);
            v_dir       := SUBSTR(f.file_name, 1, v_slash_pos);

            -- Extract filename without extension
            v_dot_pos  := INSTR(f.file_name, '.', -1);
            v_filename := SUBSTR(f.file_name, v_slash_pos + 1, v_dot_pos - v_slash_pos - 1);

            -- Strip existing numeric suffix (_01, _02 etc.) if present
            IF REGEXP_LIKE(v_filename, '_[0-9]{2}$') THEN
                v_filename := SUBSTR(v_filename, 1, LENGTH(v_filename) - 3);
            END IF;

            -- Build new datafile path
            v_new_file := v_dir || v_filename || '_' || v_next_num || '.dbf';

            DBMS_OUTPUT.PUT_LINE('  >> Will add new datafile: ' || v_new_file);

            IF v_autoextend_enable = 'YES' THEN
                EXECUTE IMMEDIATE
                    'ALTER TABLESPACE ' || f.tablespace_name ||
                    ' ADD DATAFILE ''' || v_new_file ||
                    ''' SIZE 1G AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED';
                DBMS_OUTPUT.PUT_LINE('  OK - New datafile added successfully!');
            ELSE
                DBMS_OUTPUT.PUT_LINE('  SKIPPED (v_autoextend_enable = NO)');
            END IF;

        ELSE
            -- Datafile not full yet - just fix AutoExtend MaxSize
            DBMS_OUTPUT.PUT_LINE('  >> Will execute: ALTER DATABASE DATAFILE '''
                || f.file_name || ''' AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED;');

            IF v_autoextend_enable = 'YES' THEN
                EXECUTE IMMEDIATE 'ALTER DATABASE DATAFILE '''
                    || f.file_name || ''' AUTOEXTEND ON NEXT 500M MAXSIZE UNLIMITED';
                DBMS_OUTPUT.PUT_LINE('  OK - AutoExtend set to UNLIMITED!');
            ELSE
                DBMS_OUTPUT.PUT_LINE('  SKIPPED (v_autoextend_enable = NO)');
            END IF;
        END IF;

        DBMS_OUTPUT.PUT_LINE('---');
    END LOOP;
END;

How to use:

1. Run with v_autoextend_enable := 'NO' first → review the output
2. Verify the new datafile path looks correct
3. Change to 'YES' and run again → done ✅

Note: ALTER TABLESPACE is a DDL statement — it commits automatically. No manual COMMIT needed.

---

Result

After running the script with YES, the tablespace gets a new datafile with MAXSIZE UNLIMITED. Oracle Enterprise Edition will keep growing as long as disk space is available on your server — no artificial storage limits.

This confirms that Oracle DB 26ai Enterprise Edition has no built-in storage cap. The only real limit is your disk.

---

Published on apexnote.de