ORA-01691 Unable to Extend LOB Segment - Resolution Guide

ORA-01691: Unable to Extend LOB Segment

Error Overview

Error Text: ORA-01691: unable to extend lob segment string.string in tablespace string

This error occurs when Oracle cannot allocate additional space for a LOB (Large Object) segment in its tablespace. As applications increasingly store documents, images, JSON, and XML data in LOB columns, this error has become one of the most common space-related failures in production databases.

Common Causes

1. LOB Tablespace Full

• Tablespace has no remaining free space
• Datafiles have reached maximum size
• No autoextend enabled on datafiles

2. LOB Storage Misconfiguration

• LOBs stored in the same tablespace as table data
• Default CHUNK size wasting space
• No dedicated LOB tablespace provisioned

3. LOB Fragmentation

• Frequent updates to LOB columns creating dead space
• SecureFiles vs BasicFiles storage differences
• Failed LOB operations leaving orphaned chunks

4. Large Data Loads

• Bulk INSERT of documents or images
• Migration loading large volumes of LOB data
• Application upload spikes

Diagnostic Queries

Check LOB Tablespace Usage

-- Tablespace usage including LOB segments
SELECT
  df.tablespace_name,
  ROUND(df.total_mb, 2) as total_mb,
  ROUND(df.total_mb - fs.free_mb, 2) as used_mb,
  ROUND(fs.free_mb, 2) as free_mb,
  ROUND((df.total_mb - fs.free_mb) / df.total_mb * 100, 1) as pct_used
FROM
  (SELECT tablespace_name, SUM(bytes)/1024/1024 total_mb FROM dba_data_files GROUP BY tablespace_name) df,
  (SELECT tablespace_name, SUM(bytes)/1024/1024 free_mb FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name
ORDER BY pct_used DESC;

Identify Large LOB Segments

-- Find largest LOB segments by tablespace
SELECT
  l.owner,
  l.table_name,
  l.column_name,
  l.segment_name,
  l.tablespace_name,
  s.bytes/1024/1024 as lob_size_mb,
  l.securefile,
  l.chunk
FROM dba_lobs l
JOIN dba_segments s ON l.segment_name = s.segment_name AND l.owner = s.owner
WHERE l.tablespace_name = 'YOUR_TABLESPACE'
ORDER BY s.bytes DESC;

Check LOB Space Usage Details

-- Detailed LOB space analysis (SecureFiles)
SELECT
  l.owner, l.table_name, l.column_name,
  s.segment_name, s.bytes/1024/1024 as segment_mb,
  DBMS_LOB.GETLENGTH(t.lob_column) as lob_length
FROM dba_lobs l
JOIN dba_segments s ON l.segment_name = s.segment_name
WHERE l.owner = 'YOUR_SCHEMA'
ORDER BY s.bytes DESC;

-- Check for LOB fragmentation
SELECT
  owner, table_name, column_name, segment_name,
  tablespace_name, chunk, pctversion, cache, securefile
FROM dba_lobs
WHERE owner = 'YOUR_SCHEMA';

Check Datafile Autoextend Status

-- Check if datafiles can grow
SELECT
  file_name, tablespace_name,
  bytes/1024/1024 as current_mb,
  maxbytes/1024/1024 as max_mb,
  autoextensible,
  increment_by * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024/1024 as increment_mb
FROM dba_data_files
WHERE tablespace_name = 'YOUR_LOB_TABLESPACE'
ORDER BY file_name;

Resolution Steps

Solution 1: Add Space to LOB Tablespace

-- Add a new datafile
ALTER TABLESPACE lob_data ADD DATAFILE '/u01/oradata/lob_data02.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32G;

-- Or resize existing datafile
ALTER DATABASE DATAFILE '/u01/oradata/lob_data01.dbf' RESIZE 20G;

-- Enable autoextend on existing datafile
ALTER DATABASE DATAFILE '/u01/oradata/lob_data01.dbf' AUTOEXTEND ON NEXT 1G MAXSIZE 32G;

Solution 2: Move LOBs to a Dedicated Tablespace

-- Create a dedicated LOB tablespace
CREATE TABLESPACE lob_data
  DATAFILE '/u01/oradata/lob_data01.dbf' SIZE 10G
  AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
  SEGMENT SPACE MANAGEMENT AUTO;

-- Move LOB to new tablespace (Online in 12c+)
ALTER TABLE documents MOVE LOB(doc_content) STORE AS (TABLESPACE lob_data);

-- For partitioned tables
ALTER TABLE documents MOVE PARTITION p_2024
  LOB(doc_content) STORE AS (TABLESPACE lob_data);

Solution 3: Reclaim LOB Space

-- For SecureFiles LOBs (11g+)
ALTER TABLE documents MODIFY LOB(doc_content) (SHRINK SPACE CASCADE);

-- For BasicFiles LOBs - must rebuild
ALTER TABLE documents MOVE LOB(doc_content) STORE AS (TABLESPACE lob_data);

-- Rebuild indexes after MOVE (required)
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD;'
FROM dba_indexes
WHERE table_name = 'DOCUMENTS'
  AND status = 'UNUSABLE';

Solution 4: Convert BasicFile to SecureFile

-- SecureFiles are more space-efficient with deduplication and compression
ALTER TABLE documents MOVE LOB(doc_content) STORE AS SECUREFILE (
  TABLESPACE lob_data
  DEDUPLICATE
  COMPRESS HIGH
  CACHE
);

-- Verify conversion
SELECT table_name, column_name, securefile
FROM dba_lobs
WHERE table_name = 'DOCUMENTS';

Solution 5: Purge Old LOB Data

-- Identify and remove old/unnecessary LOB data
DELETE FROM documents
WHERE created_date < ADD_MONTHS(SYSDATE, -24)
  AND status = 'ARCHIVED';
COMMIT;

-- Reclaim space after delete
ALTER TABLE documents MODIFY LOB(doc_content) (SHRINK SPACE);

-- Or for BasicFiles
ALTER TABLE documents MOVE LOB(doc_content) STORE AS (TABLESPACE lob_data);

LOB Storage Best Practices

Optimal LOB Configuration

-- Create table with properly configured LOB storage
CREATE TABLE documents (
  doc_id       NUMBER PRIMARY KEY,
  doc_name     VARCHAR2(200),
  doc_content  BLOB,
  doc_text     CLOB
)
LOB(doc_content) STORE AS SECUREFILE doc_content_lob (
  TABLESPACE lob_data
  ENABLE STORAGE IN ROW     -- Small LOBs stored inline (< 4000 bytes)
  CHUNK 32768               -- 32K chunks for large LOBs
  RETENTION AUTO
  DEDUPLICATE               -- Eliminate duplicate LOBs
  COMPRESS MEDIUM            -- Balance compression vs CPU
  CACHE READS               -- Cache for read-heavy workloads
)
LOB(doc_text) STORE AS SECUREFILE doc_text_lob (
  TABLESPACE lob_data
  ENABLE STORAGE IN ROW
  CHUNK 16384
  DEDUPLICATE
  COMPRESS HIGH              -- Text compresses well
  CACHE
);

Space Monitoring for LOBs

-- Create a monitoring query for LOB tablespace usage
SELECT
  ts.tablespace_name,
  ROUND(ts.total_mb) as total_mb,
  ROUND(ts.total_mb - ts.free_mb) as used_mb,
  ROUND(ts.free_mb) as free_mb,
  ROUND((ts.total_mb - ts.free_mb) / ts.total_mb * 100, 1) as pct_used,
  CASE
    WHEN (ts.total_mb - ts.free_mb) / ts.total_mb > 0.9 THEN 'CRITICAL'
    WHEN (ts.total_mb - ts.free_mb) / ts.total_mb > 0.8 THEN 'WARNING'
    ELSE 'OK'
  END as status
FROM (
  SELECT
    d.tablespace_name,
    SUM(d.bytes)/1024/1024 as total_mb,
    NVL(SUM(f.free_bytes)/1024/1024, 0) as free_mb
  FROM
    (SELECT tablespace_name, SUM(bytes) bytes FROM dba_data_files GROUP BY tablespace_name) d
  LEFT JOIN
    (SELECT tablespace_name, SUM(bytes) free_bytes FROM dba_free_space GROUP BY tablespace_name) f
    ON d.tablespace_name = f.tablespace_name
  GROUP BY d.tablespace_name
) ts
WHERE ts.tablespace_name IN (SELECT DISTINCT tablespace_name FROM dba_lobs)
ORDER BY pct_used DESC;

Related Errors

• ORA-01653: Unable to Extend Table - Table segment space exhaustion
• ORA-01654: Unable to Extend Index - Index segment space issues
• ORA-01688: Unable to Extend Table Partition - Partition space exhaustion
• ORA-01652: Unable to Extend Temp Segment - Temporary tablespace full
• ORA-01658: Unable to Create INITIAL Extent - Cannot create initial extent