ORA-01659: Unable to Allocate MINEXTENTS - Fix Tablespace

ORA-01659: Unable to Allocate MINEXTENTS

Error Overview

Error Text: ORA-01659: unable to allocate MINEXTENTS beyond string in tablespace string

The ORA-01659 error occurs when Oracle cannot satisfy the initial storage request for a new segment. When a table, index, partition, or other segment is created, Oracle must allocate at least MINEXTENTS extents (default 1) from the target tablespace. If the tablespace does not contain a contiguous free area large enough to satisfy the initial extent size, the segment creation fails before the object even exists.

This error is different from ORA-01653 (unable to extend an existing table) and ORA-01654 (unable to extend an existing index). ORA-01659 fires at object creation time — no segment has been created yet. The object does not exist in the database after this error.

Common Causes

1. Tablespace Is Full or Has Insufficient Contiguous Space

The tablespace contains no free extent large enough to satisfy the INITIAL storage clause or the tablespace’s default initial extent size. This is the most common cause.

2. AUTOEXTEND Is Disabled or at MAXSIZE Limit

All datafiles in the tablespace have AUTOEXTEND OFF, or they have reached their MAXSIZE limit. No automatic growth is possible.

3. INITIAL Extent Size Too Large

The object’s STORAGE (INITIAL n) clause or the tablespace’s DEFAULT STORAGE clause specifies an initial extent that cannot be satisfied by available free blocks.

4. Heavily Fragmented Tablespace

Although there may be sufficient total free space, no single contiguous free area is large enough. The tablespace contains many small, non-contiguous free extents but no large block suitable for the initial extent.

5. Locally Managed Tablespace with Uniform Extent Size

Tablespaces with EXTENT MANAGEMENT LOCAL UNIFORM SIZE n require that every allocation match the uniform extent size. If the tablespace is full, any creation fails with ORA-01659 even if the requested size is smaller than the uniform size.

6. Bigfile Tablespace Nearing Maximum Size

A bigfile tablespace with a single large datafile that has no remaining space and no AUTOEXTEND room cannot satisfy new object creation.

Diagnostic Queries

Identify the Full Tablespace

-- Free space in all tablespaces
SELECT df.tablespace_name,
       ROUND(df.total_mb, 2)          AS total_mb,
       ROUND(NVL(fs.free_mb, 0), 2)   AS free_mb,
       ROUND((df.total_mb - NVL(fs.free_mb,0)) / df.total_mb * 100, 1) AS pct_used
FROM (
  SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
  FROM dba_data_files
  GROUP BY tablespace_name
) df
LEFT JOIN (
  SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
  FROM dba_free_space
  GROUP BY tablespace_name
) fs ON df.tablespace_name = fs.tablespace_name
ORDER BY pct_used DESC NULLS FIRST;

Check Largest Contiguous Free Extent

-- Largest single free extent per tablespace
-- ORA-01659 fires when no extent is large enough for INITIAL allocation
SELECT tablespace_name,
       ROUND(MAX(bytes)/1024/1024, 2)  AS largest_free_extent_mb,
       ROUND(SUM(bytes)/1024/1024, 2)  AS total_free_mb,
       COUNT(*)                         AS free_extent_count
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY largest_free_extent_mb DESC;

Check Datafile AUTOEXTEND Status

-- Datafiles with AUTOEXTEND and remaining growth capacity
SELECT file_id, file_name, tablespace_name,
       ROUND(bytes/1024/1024, 0)     AS current_size_mb,
       ROUND(maxbytes/1024/1024, 0)  AS max_size_mb,
       autoextensible,
       ROUND((maxbytes - bytes)/1024/1024, 0) AS remaining_autoextend_mb
FROM dba_data_files
ORDER BY tablespace_name, file_id;

Check Default Storage for the Tablespace

-- Tablespace properties including default extent management
SELECT tablespace_name, extent_management, allocation_type,
       initial_extent, next_extent, min_extents,
       segment_space_management, status
FROM dba_tablespaces
WHERE tablespace_name = UPPER('&tablespace_name');

Estimate the Required INITIAL Extent Size

-- What storage clause does the failing CREATE statement specify?
-- If you know the table/index name that failed, look at the DDL:
SELECT dbms_metadata.get_ddl('TABLE', 'TABLE_NAME', 'SCHEMA_NAME') FROM DUAL;

-- Or check what the tablespace default requires
SELECT tablespace_name, initial_extent/1024/1024 AS initial_mb
FROM dba_tablespaces
WHERE tablespace_name = UPPER('&tablespace_name');

Step-by-Step Resolution

1. Add a Datafile to the Tablespace (Most Common Fix)

-- Add a new datafile with AUTOEXTEND
ALTER TABLESPACE users
ADD DATAFILE '/u01/oradata/mydb/users02.dbf'
SIZE 1G AUTOEXTEND ON NEXT 256M MAXSIZE 10G;

-- Verify new free space
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,0) AS free_mb
FROM dba_free_space
WHERE tablespace_name = 'USERS'
GROUP BY tablespace_name;

2. Resize an Existing Datafile

-- Increase size of an existing datafile
ALTER DATABASE DATAFILE '/u01/oradata/mydb/users01.dbf' RESIZE 4G;

-- Enable AUTOEXTEND on a file that had it disabled
ALTER DATABASE DATAFILE '/u01/oradata/mydb/users01.dbf'
AUTOEXTEND ON NEXT 256M MAXSIZE 10G;

3. Enable AUTOEXTEND on All Datafiles in the Tablespace

-- Script to enable AUTOEXTEND on all files in a tablespace
BEGIN
  FOR f IN (SELECT file_id, file_name
            FROM dba_data_files
            WHERE tablespace_name = 'USERS'
              AND autoextensible = 'NO') LOOP
    EXECUTE IMMEDIATE
      'ALTER DATABASE DATAFILE ''' || f.file_name ||
      ''' AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED';
  END LOOP;
END;
/

4. Adjust the Storage Clause of the Failing Object

If you cannot immediately add space, reduce the INITIAL extent size in the CREATE statement:

-- Original failing statement might be:
CREATE TABLE big_table (id NUMBER, data VARCHAR2(4000))
TABLESPACE users
STORAGE (INITIAL 500M NEXT 100M);

-- Reduce INITIAL to fit available contiguous space:
CREATE TABLE big_table (id NUMBER, data VARCHAR2(4000))
TABLESPACE users
STORAGE (INITIAL 64M NEXT 64M);

5. Defragment a Fragmented Tablespace

When total free space exists but no single extent is large enough, defragmentation helps (though in locally managed tablespaces with uniform extent management, this is rarely needed):

-- For dictionary-managed tablespaces (rare in modern systems):
ALTER TABLESPACE users COALESCE;

-- For locally managed tablespaces, adding a datafile is the correct approach
-- as Oracle cannot coalesce non-contiguous free extents across different positions

6. Move Objects to a Different Tablespace

If the target tablespace is permanently full and cannot be extended:

-- Move a table to another tablespace
ALTER TABLE schema.table_name MOVE TABLESPACE new_tablespace;

-- Rebuild indexes after moving the table (they become unusable)
ALTER INDEX schema.index_name REBUILD TABLESPACE new_tablespace;

7. Create the Object with COMPRESS to Reduce Initial Footprint

-- Table compression reduces initial space requirement
CREATE TABLE compressed_table (
  id     NUMBER,
  data   VARCHAR2(4000)
)
COMPRESS FOR ALL OPERATIONS
TABLESPACE users;

Prevention Strategies

1. Proactive Tablespace Space Monitoring

-- Daily check for tablespaces below 15% free
SELECT tablespace_name,
       ROUND(total_mb, 0) AS total_mb,
       ROUND(free_mb, 0)  AS free_mb,
       ROUND(free_mb / total_mb * 100, 1) AS pct_free
FROM (
  SELECT d.tablespace_name,
         SUM(d.bytes)/1024/1024 AS total_mb,
         NVL(SUM(f.bytes),0)/1024/1024 AS free_mb
  FROM dba_data_files d
  LEFT JOIN dba_free_space f USING (tablespace_name)
  GROUP BY d.tablespace_name
)
WHERE free_mb / total_mb * 100 < 15
ORDER BY pct_free;

2. Always Use AUTOEXTEND for Non-Critical Tablespaces

-- Template for adding datafiles with sensible AUTOEXTEND settings
ALTER TABLESPACE &tablespace_name
ADD DATAFILE '&datafile_path'
SIZE 1G
AUTOEXTEND ON NEXT 512M MAXSIZE 32G;

3. Standardize on Locally Managed Tablespaces with AUTOALLOCATE

-- Recommended tablespace creation template
CREATE TABLESPACE app_data
DATAFILE '/u01/oradata/mydb/app_data01.dbf'
SIZE 2G AUTOEXTEND ON NEXT 512M MAXSIZE 50G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

4. Schedule a Space Alert Job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'TABLESPACE_SPACE_ALERT',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
      DECLARE
        v_count NUMBER;
      BEGIN
        SELECT COUNT(*) INTO v_count
        FROM (
          SELECT tablespace_name,
                 SUM(bytes) AS free_bytes
          FROM dba_free_space
          GROUP BY tablespace_name
          HAVING SUM(bytes) < 104857600  -- less than 100 MB free
        );
        IF v_count > 0 THEN
          INSERT INTO dba_alerts(alert_time, alert_msg)
          VALUES (SYSDATE, v_count || ' tablespace(s) under 100MB free — ORA-01659 risk');
          COMMIT;
        END IF;
      END;
    ]',
    repeat_interval => 'FREQ=HOURLY',
    enabled         => TRUE
  );
END;
/

Related Errors

• ORA-01652 - Unable to Extend Temp Segment (temporary tablespace)
• ORA-01653 - Unable to Extend Table (extending existing table)
• ORA-01654 - Unable to Extend Index (extending existing index)
• ORA-01688 - Unable to Extend Table Partition
• ORA-01658 - Unable to Create INITIAL Extent for Segment in Rollback Tablespace

Emergency Response

Quick Fixes

1. Add a datafile immediately

   ALTER TABLESPACE &tbs_name
   ADD DATAFILE '/u01/oradata/&sid/&tbs_name_02.dbf'
   SIZE 2G AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED;

2. Resize existing datafile

   ALTER DATABASE DATAFILE '&datafile_path' RESIZE &new_size_G G;

3. Retry the failing CREATE with smaller INITIAL

   -- Add STORAGE (INITIAL 64M) to the CREATE TABLE/INDEX statement

Post-Resolution Cleanup

-- Confirm tablespace now has sufficient free space
SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024, 0) AS free_mb
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY free_mb;

-- Confirm the object that failed was created successfully
SELECT object_name, object_type, status, created
FROM dba_objects
WHERE object_name = UPPER('&failed_object')
  AND owner = UPPER('&schema_name');

-- Enable AUTOEXTEND across all datafiles to prevent recurrence
-- Review and update capacity planning for the affected tablespace