ORA-01658 Unable to Create INITIAL Extent - Tablespace Management Guide

ORA-01658: Unable to Create INITIAL Extent for Segment in Tablespace

Error Overview

Error Text: ORA-01658: unable to create INITIAL extent for segment in tablespace string

This error occurs when Oracle cannot allocate the initial extent for a new segment (table, index, or other database object) in the specified tablespace. Unlike ORA-01653 which deals with extending existing segments, ORA-01658 prevents the creation of new objects entirely.

Understanding Initial Extents

What is an Initial Extent?

• The first extent allocated when creating a database segment
• Size determined by INITIAL parameter or default tablespace settings
• Must be allocated from contiguous free space
• Cannot be created if insufficient contiguous space exists

Common Scenarios

-- These operations can trigger ORA-01658:
CREATE TABLE large_table (...) STORAGE (INITIAL 500M);  -- Need 500MB contiguous
CREATE INDEX big_index ON table(...);                   -- Index needs initial extent
ALTER TABLE table ADD CONSTRAINT pk PRIMARY KEY(...);   -- PK index needs space
CREATE TEMPORARY TABLE temp_data (...);                 -- Temp segments need space

Root Causes

1. Insufficient Free Space

• Tablespace has less total free space than required initial extent
• Free space exists but not in contiguous chunks
• Tablespace approaching maximum size limits

2. Fragmentation Issues

• Multiple small free space chunks
• No single contiguous area large enough
• Result of many object drops and creates

3. Poor Storage Parameters

• INITIAL extent size too large for tablespace
• Unrealistic storage requirements
• Mismatch between object needs and space availability

4. Temporary Tablespace Issues

• Temp tablespace full during sorts/joins
• Insufficient temp space for index builds
• Multiple sessions competing for temp space

Diagnostic Queries

Check Tablespace Free Space

-- Overall tablespace usage
SELECT
  df.tablespace_name,
  ROUND(df.total_mb, 2) as total_mb,
  ROUND(df.total_mb - NVL(fs.free_mb, 0), 2) as used_mb,
  ROUND(NVL(fs.free_mb, 0), 2) as free_mb,
  ROUND((df.total_mb - NVL(fs.free_mb, 0)) / df.total_mb * 100, 2) as used_pct
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 used_pct DESC;

-- Check free space fragmentation
SELECT
  tablespace_name,
  COUNT(*) as free_chunks,
  ROUND(MAX(bytes)/1024/1024, 2) as largest_mb,
  ROUND(MIN(bytes)/1024/1024, 2) as smallest_mb,
  ROUND(AVG(bytes)/1024/1024, 2) as avg_chunk_mb,
  ROUND(SUM(bytes)/1024/1024, 2) as total_free_mb
FROM dba_free_space
WHERE tablespace_name = 'YOUR_TABLESPACE'
GROUP BY tablespace_name;

-- Identify fragmentation level
SELECT
  tablespace_name,
  SQRT(MAX(blocks)/SUM(blocks)) * 100 as fragmentation_index,
  COUNT(blocks) as free_chunks
FROM dba_free_space
GROUP BY tablespace_name
ORDER BY fragmentation_index DESC;

Analyze Storage Parameters

-- Check default storage for tablespace
SELECT tablespace_name, initial_extent, next_extent,
       min_extents, max_extents, pct_increase
FROM dba_tablespaces
WHERE tablespace_name = 'YOUR_TABLESPACE';

-- Find objects with large storage requirements
SELECT owner, segment_name, segment_type, tablespace_name,
       initial_extent/1024/1024 as initial_mb,
       next_extent/1024/1024 as next_mb,
       extents, max_extents
FROM dba_segments
WHERE tablespace_name = 'YOUR_TABLESPACE'
  AND initial_extent > 100*1024*1024  -- > 100MB
ORDER BY initial_extent DESC;

Check Temporary Tablespace Usage

-- Current temp usage
SELECT
  s.username, s.sid, s.serial#,
  t.tablespace, t.segtype,
  ROUND(t.blocks * 8192 / 1024 / 1024, 2) as temp_mb
FROM v$session s
JOIN v$tempseg_usage t ON s.saddr = t.session_addr
ORDER BY t.blocks DESC;

-- Temp tablespace sizing
SELECT
  tablespace_name,
  ROUND(tablespace_size * 8192 / 1024 / 1024, 2) as total_mb,
  ROUND(allocated_space * 8192 / 1024 / 1024, 2) as allocated_mb,
  ROUND(free_space * 8192 / 1024 / 1024, 2) as free_mb
FROM dba_temp_free_space;

Resolution Strategies

1. Add Space to Tablespace

Add New Datafile

-- Add datafile to existing tablespace
ALTER TABLESPACE your_tablespace
ADD DATAFILE '/u01/oradata/your_db/your_tablespace_02.dbf'
SIZE 10G AUTOEXTEND ON MAXSIZE 32G;

-- For temporary tablespaces
ALTER TABLESPACE temp
ADD TEMPFILE '/u01/oradata/your_db/temp_02.dbf'
SIZE 5G AUTOEXTEND ON MAXSIZE 10G;

Resize Existing Datafiles

-- Check current datafile sizes and max sizes
SELECT file_name, tablespace_name,
       ROUND(bytes/1024/1024, 2) as current_mb,
       ROUND(maxbytes/1024/1024, 2) as max_mb,
       autoextensible
FROM dba_data_files
WHERE tablespace_name = 'YOUR_TABLESPACE'
ORDER BY file_id;

-- Resize datafile
ALTER DATABASE DATAFILE '/path/to/datafile.dbf'
RESIZE 20G;

-- Enable autoextend
ALTER DATABASE DATAFILE '/path/to/datafile.dbf'
AUTOEXTEND ON MAXSIZE 32G;

2. Reduce Fragmentation

Coalesce Free Space

-- Coalesce adjacent free space
ALTER TABLESPACE your_tablespace COALESCE;

-- Check improvement
SELECT tablespace_name, COUNT(*) as free_chunks
FROM dba_free_space
WHERE tablespace_name = 'YOUR_TABLESPACE'
GROUP BY tablespace_name;

Rebuild Tablespace (Advanced)

-- Create new tablespace
CREATE TABLESPACE your_tablespace_new
DATAFILE '/u01/oradata/your_db/your_tablespace_new_01.dbf'
SIZE 10G AUTOEXTEND ON MAXSIZE 32G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

-- Move objects to new tablespace
ALTER TABLE schema.table_name MOVE TABLESPACE your_tablespace_new;
ALTER INDEX schema.index_name REBUILD TABLESPACE your_tablespace_new;

-- Update default tablespace for users
ALTER USER username DEFAULT TABLESPACE your_tablespace_new;

-- Drop old tablespace after moving all objects
DROP TABLESPACE your_tablespace INCLUDING CONTENTS AND DATAFILES;

-- Rename new tablespace
ALTER TABLESPACE your_tablespace_new RENAME TO your_tablespace;

3. Adjust Storage Parameters

Modify Object Storage Requirements

-- Reduce initial extent size for new objects
CREATE TABLE test_table (
  id NUMBER,
  data VARCHAR2(100)
) STORAGE (
  INITIAL 1M      -- Start smaller
  NEXT 1M
  PCTINCREASE 50  -- Allow growth as needed
);

-- Modify existing table storage (affects future extents)
ALTER TABLE schema.table_name STORAGE (NEXT 5M PCTINCREASE 0);

Use Different Tablespace

-- Create object in different tablespace with more space
CREATE TABLE large_table (...) TABLESPACE large_objects_ts;

-- Move existing object
ALTER TABLE schema.table_name MOVE TABLESPACE new_tablespace;

4. Temporary Solutions

Create Smaller Initial Extent

-- Create table with smaller initial extent, then extend
CREATE TABLE temp_solution (
  col1 NUMBER,
  col2 VARCHAR2(4000)
) STORAGE (INITIAL 64K);  -- Very small initial

-- Then use ALTER to add space as needed through normal operations
INSERT INTO temp_solution VALUES (1, 'test');  -- This may trigger extent allocation

Use NOLOGGING for Large Operations

-- Reduce redo generation for large object creation
CREATE TABLE large_table NOLOGGING
AS SELECT * FROM source_table;

-- Remember to backup after NOLOGGING operations
-- ALTER TABLE large_table LOGGING;

Prevention Strategies

1. Proactive Space Management

-- Create space monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_tablespace_space AS
BEGIN
  FOR rec IN (
    SELECT tablespace_name,
           ROUND((total_mb - free_mb)/total_mb * 100, 2) as used_pct,
           ROUND(free_mb, 2) as free_mb
    FROM (
      SELECT df.tablespace_name,
             SUM(df.bytes)/1024/1024 as total_mb,
             NVL(SUM(fs.bytes), 0)/1024/1024 as free_mb
      FROM dba_data_files df
      LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name
      GROUP BY df.tablespace_name
    )
    WHERE (total_mb - free_mb)/total_mb * 100 > 85  -- > 85% full
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('WARNING: Tablespace ' || rec.tablespace_name ||
                        ' is ' || rec.used_pct || '% full');
    DBMS_OUTPUT.PUT_LINE('Free space: ' || rec.free_mb || ' MB');
  END LOOP;
END;
/

-- Schedule monitoring job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'TABLESPACE_SPACE_CHECK',
    job_type => 'STORED_PROCEDURE',
    job_action => 'monitor_tablespace_space',
    repeat_interval => 'FREQ=HOURLY; BYMINUTE=0',
    enabled => TRUE
  );
END;
/

2. Optimal Storage Configuration

-- Create tablespace with appropriate settings
CREATE TABLESPACE application_data
DATAFILE '/u01/oradata/prod/app_data_01.dbf' SIZE 10G,
         '/u01/oradata/prod/app_data_02.dbf' SIZE 10G
AUTOEXTEND ON MAXSIZE 32G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE  -- Let Oracle manage extents
SEGMENT SPACE MANAGEMENT AUTO         -- Automatic space management
DEFAULT STORAGE (
  INITIAL 1M
  NEXT 1M
  PCTINCREASE 0
);

-- Set reasonable quotas
ALTER USER app_user QUOTA 20G ON application_data;

3. Capacity Planning

-- Growth trend analysis
WITH monthly_growth AS (
  SELECT
    TO_CHAR(sample_time, 'YYYY-MM') as month,
    tablespace_name,
    MAX(tablespace_size * 8192 / 1024 / 1024) as max_size_mb
  FROM dba_hist_tbspc_space_usage
  WHERE tablespace_name = 'APPLICATION_DATA'
    AND sample_time > SYSDATE - 180  -- Last 6 months
  GROUP BY TO_CHAR(sample_time, 'YYYY-MM'), tablespace_name
)
SELECT month, tablespace_name, max_size_mb,
       max_size_mb - LAG(max_size_mb) OVER (ORDER BY month) as growth_mb
FROM monthly_growth
ORDER BY month;

-- Predict when tablespace will be full
SELECT tablespace_name,
       ROUND(free_mb, 2) as current_free_mb,
       ROUND(growth_per_day, 2) as daily_growth_mb,
       CASE
         WHEN growth_per_day > 0 THEN
           ROUND(free_mb / growth_per_day, 0)
         ELSE
           NULL
       END as days_until_full
FROM (
  SELECT tablespace_name,
         (total_mb - used_mb) as free_mb,
         -- Calculate daily growth from AWR
         (SELECT AVG(bytes_delta) / 1024 / 1024
          FROM dba_hist_seg_stat
          WHERE tablespace_name = ts.tablespace_name
            AND snap_id > (SELECT MAX(snap_id) - 7 FROM dba_hist_snapshot)
         ) as growth_per_day
  FROM (
    SELECT df.tablespace_name,
           SUM(df.bytes)/1024/1024 as total_mb,
           SUM(df.bytes - NVL(fs.bytes, 0))/1024/1024 as used_mb
    FROM dba_data_files df
    LEFT JOIN dba_free_space fs ON df.file_id = fs.file_id
    GROUP BY df.tablespace_name
  ) ts
);

Best Practices

1. Tablespace Design

• Use locally managed tablespaces with AUTOALLOCATE
• Enable autoextend with reasonable MAXSIZE limits
• Distribute objects across multiple tablespaces by function
• Separate indexes from tables where possible

2. Object Creation

-- Good practice: Estimate space needs before creating objects
SELECT COUNT(*) * AVG_ROW_LEN * 1.3 / 1024 / 1024 as estimated_mb
FROM user_tables
WHERE table_name = 'SIMILAR_TABLE';

-- Create with appropriate storage
CREATE TABLE new_table (...)
STORAGE (INITIAL 100M NEXT 10M PCTINCREASE 0)
TABLESPACE appropriate_tablespace;

3. Monitoring and Alerts

• Monitor tablespace usage daily
• Set alerts at 80% and 90% capacity
• Track fragmentation levels weekly
• Plan capacity additions proactively

Related Errors

• ORA-01653 - Unable to extend table (existing objects)
• ORA-01654 - Unable to extend index
• ORA-01652 - Unable to extend temp segment
• ORA-01688 - Unable to extend table partition
• ORA-30036 - Unable to extend segment in undo tablespace

Emergency Response

Quick Actions

1. Add emergency space

   ALTER TABLESPACE problem_tablespace
   ADD DATAFILE '/tmp/emergency.dbf' SIZE 5G;

2. Enable autoextend on existing files

   ALTER DATABASE DATAFILE '/path/to/datafile.dbf'
   AUTOEXTEND ON MAXSIZE UNLIMITED;

3. Drop unnecessary objects

   DROP TABLE schema.temp_table_not_needed;
   DROP INDEX schema.unused_index;

4. Move objects to different tablespace

   ALTER TABLE large_table MOVE TABLESPACE tablespace_with_space;

5. Coalesce free space

   ALTER TABLESPACE fragmented_tablespace COALESCE;

The key is to quickly provide space while planning a longer-term solution for proper space management.