ORA-01654 Unable to Extend Index - Index Tablespace Management

ORA-01654: Unable to Extend Index

Error Overview

Error Text: ORA-01654: unable to extend index [schema].[index] by [blocks] in tablespace [tablespace_name]

This error occurs when Oracle cannot allocate additional space for an index because the tablespace containing the index is full or cannot extend. This typically happens during index creation, rebuilding, or when the index grows due to data insertion or updates.

Understanding the Error

Index Space Requirements

Index Operation (CREATE/REBUILD/INSERT)
       ↓
Index Needs More Space
       ↓
Request Extent from Index Tablespace
       ↓
Check Available Space
├── Free Space Available → Allocate Extent ✓
└── No Free Space → ORA-01654 Error ✗

Common Scenarios

• Index creation - Creating new indexes on large tables
• Index rebuild - Rebuilding fragmented indexes
• Bulk data loads - Large INSERT operations causing index growth
• Index partition maintenance - Partition split/merge operations

Diagnostic Steps

1. Identify the Problem Index and Tablespace

-- Find the specific index from the error message
SELECT
  owner,
  index_name,
  table_name,
  tablespace_name,
  status,
  uniqueness,
  index_type,
  ROUND(last_analyzed - created) as days_since_creation
FROM dba_indexes
WHERE owner = 'SCHEMA_NAME'  -- From error message
  AND index_name = 'INDEX_NAME';  -- From error message

-- Check index tablespace usage
SELECT
  tablespace_name,
  ROUND(total_mb, 2) as total_mb,
  ROUND(used_mb, 2) as used_mb,
  ROUND(free_mb, 2) as free_mb,
  ROUND(used_pct, 2) as used_pct,
  autoextensible
FROM (
  SELECT
    df.tablespace_name,
    SUM(df.bytes)/1024/1024 as total_mb,
    SUM(df.bytes)/1024/1024 - NVL(SUM(fs.bytes)/1024/1024, 0) as used_mb,
    NVL(SUM(fs.bytes)/1024/1024, 0) as free_mb,
    ((SUM(df.bytes)/1024/1024 - NVL(SUM(fs.bytes)/1024/1024, 0)) /
     (SUM(df.bytes)/1024/1024)) * 100 as used_pct,
    CASE WHEN COUNT(CASE WHEN df.autoextensible = 'YES' THEN 1 END) > 0
         THEN 'YES' ELSE 'NO' END as autoextensible
  FROM dba_data_files df
  LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name
  WHERE df.tablespace_name = 'INDEX_TABLESPACE'  -- From error message
  GROUP BY df.tablespace_name
);

-- Check largest free extent available
SELECT
  tablespace_name,
  ROUND(MAX(bytes)/1024/1024, 2) as largest_free_mb,
  COUNT(*) as free_chunks,
  ROUND(AVG(bytes)/1024/1024, 2) as avg_free_mb
FROM dba_free_space
WHERE tablespace_name = 'INDEX_TABLESPACE'
GROUP BY tablespace_name;

2. Analyze Index Size and Growth

-- Check current index size and extents
SELECT
  owner,
  segment_name as index_name,
  ROUND(bytes/1024/1024, 2) as size_mb,
  extents,
  max_extents,
  ROUND(next_extent/1024/1024, 2) as next_extent_mb,
  pct_increase
FROM dba_segments
WHERE owner = 'SCHEMA_NAME'
  AND segment_name = 'INDEX_NAME'
  AND segment_type LIKE 'INDEX%';

-- Check index statistics for size estimation
SELECT
  i.owner,
  i.index_name,
  i.table_name,
  i.num_rows,
  i.distinct_keys,
  i.leaf_blocks,
  i.avg_leaf_blocks_per_key,
  i.clustering_factor,
  ROUND(i.leaf_blocks * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024, 2) as estimated_size_mb
FROM dba_indexes i
WHERE i.owner = 'SCHEMA_NAME'
  AND i.index_name = 'INDEX_NAME';

-- Check underlying table size
SELECT
  owner,
  table_name,
  num_rows,
  ROUND(avg_row_len) as avg_row_len,
  ROUND((num_rows * avg_row_len)/1024/1024, 2) as table_size_mb,
  last_analyzed
FROM dba_tables
WHERE owner = 'SCHEMA_NAME'
  AND table_name = (
    SELECT table_name FROM dba_indexes
    WHERE owner = 'SCHEMA_NAME' AND index_name = 'INDEX_NAME'
  );

3. Check Current Operations

-- Check for ongoing index operations
SELECT
  sid,
  serial#,
  username,
  sql_id,
  operation,
  target,
  ROUND(elapsed_seconds/60, 2) as elapsed_minutes,
  ROUND(time_remaining/60, 2) as remaining_minutes,
  ROUND(sofar/totalwork*100, 2) as pct_complete,
  message
FROM v$session_longops
WHERE target LIKE '%INDEX_NAME%'
   OR operation LIKE '%INDEX%'
ORDER BY start_time DESC;

-- Check for DDL locks on the index
SELECT
  s.sid,
  s.serial#,
  s.username,
  l.type,
  l.mode_held,
  l.mode_requested,
  o.object_name,
  o.object_type
FROM v$lock l, v$session s, dba_objects o
WHERE l.sid = s.sid
  AND l.id1 = o.object_id
  AND o.object_name = 'INDEX_NAME'
  AND o.owner = 'SCHEMA_NAME';

Immediate Solutions

Solution 1: Add Space to Index Tablespace

Add New Datafile

-- Add new datafile to index tablespace
ALTER TABLESPACE idx_tablespace
ADD DATAFILE '/u01/app/oracle/oradata/orcl/idx_tablespace02.dbf'
SIZE 2G
AUTOEXTEND ON
NEXT 100M
MAXSIZE 20G;

-- For Oracle Managed Files (OMF)
ALTER TABLESPACE idx_tablespace
ADD DATAFILE
SIZE 2G
AUTOEXTEND ON
NEXT 100M
MAXSIZE 20G;

-- Verify the addition
SELECT
  file_name,
  ROUND(bytes/1024/1024, 2) as size_mb,
  autoextensible,
  ROUND(maxbytes/1024/1024, 2) as max_size_mb
FROM dba_data_files
WHERE tablespace_name = 'IDX_TABLESPACE'
ORDER BY file_id;

Resize Existing Datafiles

-- Check current datafile sizes
SELECT
  file_name,
  ROUND(bytes/1024/1024, 2) as current_size_mb,
  ROUND(maxbytes/1024/1024, 2) as max_size_mb,
  autoextensible,
  ROUND(increment_by * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 / 1024, 2) as increment_mb
FROM dba_data_files
WHERE tablespace_name = 'IDX_TABLESPACE';

-- Resize datafile
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/idx_tablespace01.dbf'
RESIZE 5G;

-- Enable autoextend
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/idx_tablespace01.dbf'
AUTOEXTEND ON
NEXT 100M
MAXSIZE 20G;

Solution 2: Move Index to Different Tablespace

Rebuild Index in New Tablespace

-- Check space in target tablespace
SELECT
  tablespace_name,
  ROUND(SUM(bytes)/1024/1024, 2) as free_mb
FROM dba_free_space
WHERE tablespace_name = 'INDEXES2'  -- Target tablespace
GROUP BY tablespace_name;

-- Rebuild index in new tablespace
ALTER INDEX schema.index_name REBUILD TABLESPACE indexes2;

-- For large indexes, use online rebuild
ALTER INDEX schema.index_name REBUILD TABLESPACE indexes2 ONLINE;

-- For partitioned indexes, rebuild specific partitions
ALTER INDEX schema.partitioned_index
REBUILD PARTITION partition_name TABLESPACE indexes2;

-- Check if rebuild was successful
SELECT
  owner,
  index_name,
  tablespace_name,
  status
FROM dba_indexes
WHERE owner = 'SCHEMA'
  AND index_name = 'INDEX_NAME';

Solution 3: Optimize Index Storage

Rebuild with Better Storage Parameters

-- Check current storage parameters
SELECT
  index_name,
  initial_extent,
  next_extent,
  pct_increase,
  max_extents
FROM dba_indexes
WHERE owner = 'SCHEMA'
  AND index_name = 'INDEX_NAME';

-- Rebuild with optimized storage
ALTER INDEX schema.index_name REBUILD
STORAGE (
  INITIAL 100M
  NEXT 100M
  PCTINCREASE 0
  MAXEXTENTS UNLIMITED
);

-- For better performance with large indexes
ALTER INDEX schema.index_name REBUILD
STORAGE (
  INITIAL 500M
  NEXT 500M
  PCTINCREASE 0
)
NOLOGGING  -- Faster rebuild, but no recovery
PARALLEL 4;  -- Use parallel processing

-- Re-enable logging after rebuild
ALTER INDEX schema.index_name LOGGING;
ALTER INDEX schema.index_name NOPARALLEL;

Long-Term Solutions

1. Proper Index Tablespace Management

Create Dedicated Index Tablespaces

-- Create separate tablespace for indexes
CREATE TABLESPACE indexes_large
DATAFILE '/u01/app/oracle/oradata/orcl/indexes_large01.dbf' SIZE 5G,
         '/u01/app/oracle/oradata/orcl/indexes_large02.dbf' SIZE 5G
AUTOEXTEND ON NEXT 100M MAXSIZE 50G
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 10M  -- Larger uniform extents for indexes
SEGMENT SPACE MANAGEMENT AUTO;

-- Create tablespace for small indexes
CREATE TABLESPACE indexes_small
DATAFILE '/u01/app/oracle/oradata/orcl/indexes_small01.dbf' SIZE 2G
AUTOEXTEND ON NEXT 50M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

Implement Tablespace Monitoring

-- Create index tablespace monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_index_tablespaces AS
  CURSOR idx_ts_cursor IS
    SELECT DISTINCT tablespace_name
    FROM dba_indexes
    WHERE tablespace_name IS NOT NULL
      AND owner NOT IN ('SYS', 'SYSTEM');

  v_used_pct NUMBER;
  v_free_mb NUMBER;
BEGIN
  FOR ts_rec IN idx_ts_cursor LOOP
    -- Calculate usage for each index tablespace
    SELECT
      ROUND(((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes)) * 100, 2),
      ROUND(NVL(SUM(fs.bytes)/1024/1024, 0), 2)
    INTO v_used_pct, v_free_mb
    FROM dba_data_files df
    LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name
    WHERE df.tablespace_name = ts_rec.tablespace_name;

    -- Alert if usage is high
    IF v_used_pct > 85 THEN
      INSERT INTO dba_tablespace_alerts (
        tablespace_name, used_pct, free_mb, alert_level, details
      ) VALUES (
        ts_rec.tablespace_name, v_used_pct, v_free_mb, 'WARNING',
        'Index tablespace usage high - consider adding space'
      );

      -- Auto-extend if critical
      IF v_used_pct > 95 THEN
        BEGIN
          EXECUTE IMMEDIATE 'ALTER TABLESPACE ' || ts_rec.tablespace_name ||
                          ' ADD DATAFILE SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G';

          UPDATE dba_tablespace_alerts
          SET action_taken = 'Added 1GB datafile automatically'
          WHERE tablespace_name = ts_rec.tablespace_name
            AND alert_time = (SELECT MAX(alert_time) FROM dba_tablespace_alerts
                             WHERE tablespace_name = ts_rec.tablespace_name);
        EXCEPTION
          WHEN OTHERS THEN
            UPDATE dba_tablespace_alerts
            SET action_taken = 'Failed to add datafile: ' || SQLERRM
            WHERE tablespace_name = ts_rec.tablespace_name
              AND alert_time = (SELECT MAX(alert_time) FROM dba_tablespace_alerts
                               WHERE tablespace_name = ts_rec.tablespace_name);
        END;
      END IF;
    END IF;
  END LOOP;

  COMMIT;
END;
/

-- Schedule the monitoring
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'MONITOR_INDEX_TABLESPACES',
    job_type => 'STORED_PROCEDURE',
    job_action => 'monitor_index_tablespaces',
    repeat_interval => 'FREQ=HOURLY',
    enabled => TRUE
  );
END;
/

2. Index Maintenance Strategy

Regular Index Analysis and Maintenance

-- Create procedure to analyze index health
CREATE OR REPLACE PROCEDURE analyze_index_health AS
  CURSOR idx_cursor IS
    SELECT owner, index_name, table_name, tablespace_name
    FROM dba_indexes
    WHERE owner NOT IN ('SYS', 'SYSTEM')
      AND status = 'VALID';

  v_height NUMBER;
  v_pct_deleted NUMBER;
  v_clustering_factor NUMBER;
  v_num_rows NUMBER;
BEGIN
  FOR idx_rec IN idx_cursor LOOP
    -- Analyze index structure
    BEGIN
      EXECUTE IMMEDIATE 'ANALYZE INDEX ' || idx_rec.owner || '.' ||
                       idx_rec.index_name || ' VALIDATE STRUCTURE';

      -- Get index statistics
      SELECT height, pct_deleted_keys, clustering_factor
      INTO v_height, v_pct_deleted, v_clustering_factor
      FROM index_stats
      WHERE name = idx_rec.index_name;

      -- Get table row count
      SELECT num_rows INTO v_num_rows
      FROM dba_tables
      WHERE owner = idx_rec.owner AND table_name = idx_rec.table_name;

      -- Check for rebuild candidates
      IF v_height > 4 OR v_pct_deleted > 20 OR
         (v_clustering_factor > v_num_rows * 2 AND v_num_rows > 100000) THEN

        INSERT INTO dba_index_maintenance_log (
          log_date, owner, index_name, table_name,
          height, pct_deleted, clustering_factor, recommendation
        ) VALUES (
          SYSDATE, idx_rec.owner, idx_rec.index_name, idx_rec.table_name,
          v_height, v_pct_deleted, v_clustering_factor, 'REBUILD_RECOMMENDED'
        );
      END IF;

    EXCEPTION
      WHEN OTHERS THEN
        -- Log errors but continue
        INSERT INTO dba_index_maintenance_log (
          log_date, owner, index_name, table_name, error_message
        ) VALUES (
          SYSDATE, idx_rec.owner, idx_rec.index_name, idx_rec.table_name, SQLERRM
        );
    END;
  END LOOP;

  COMMIT;
END;
/

-- Create maintenance log table
CREATE TABLE dba_index_maintenance_log (
  log_date DATE,
  owner VARCHAR2(30),
  index_name VARCHAR2(30),
  table_name VARCHAR2(30),
  height NUMBER,
  pct_deleted NUMBER,
  clustering_factor NUMBER,
  recommendation VARCHAR2(50),
  error_message VARCHAR2(500)
);

3. Preventive Space Planning

Calculate Index Space Requirements

-- Estimate space requirements for new indexes
CREATE OR REPLACE FUNCTION estimate_index_size(
  p_owner VARCHAR2,
  p_table_name VARCHAR2,
  p_column_list VARCHAR2
) RETURN NUMBER AS
  v_num_rows NUMBER;
  v_avg_key_length NUMBER;
  v_estimated_size NUMBER;
BEGIN
  -- Get table statistics
  SELECT num_rows INTO v_num_rows
  FROM dba_tables
  WHERE owner = p_owner AND table_name = p_table_name;

  -- Estimate average key length (simplified calculation)
  -- In practice, you'd calculate based on actual column data types
  v_avg_key_length := 20;  -- Assume 20 bytes average

  -- Calculate estimated index size in MB
  -- Formula: (num_rows * (avg_key_length + 6 + rowid)) * 1.2 / 1024 / 1024
  v_estimated_size := (v_num_rows * (v_avg_key_length + 6 + 10)) * 1.2 / 1024 / 1024;

  RETURN v_estimated_size;
END;
/

-- Use the function to estimate before creating indexes
SELECT
  'CREATE INDEX idx_' || table_name || '_' || column_name ||
  ' ON ' || owner || '.' || table_name || '(' || column_name || ') TABLESPACE indexes_large;' as ddl,
  estimate_index_size(owner, table_name, column_name) as estimated_size_mb
FROM dba_tab_columns
WHERE owner = 'MYSCHEMA'
  AND table_name = 'LARGE_TABLE'
  AND column_name IN ('COLUMN1', 'COLUMN2');

Specialized Scenarios

1. Partitioned Index Management

-- Handle partitioned index space issues
SELECT
  ip.index_owner,
  ip.index_name,
  ip.partition_name,
  ip.tablespace_name,
  ds.bytes/1024/1024 as size_mb
FROM dba_ind_partitions ip, dba_segments ds
WHERE ip.index_owner = ds.owner
  AND ip.index_name = ds.segment_name
  AND ip.partition_name = ds.partition_name
  AND ip.index_owner = 'SCHEMA'
  AND ip.index_name = 'PARTITIONED_INDEX'
ORDER BY ds.bytes DESC;

-- Rebuild specific partitions
ALTER INDEX schema.partitioned_index
REBUILD PARTITION p_2023_01 TABLESPACE indexes_2023;

-- Move partition to different tablespace
ALTER INDEX schema.partitioned_index
MODIFY PARTITION p_2023_01 REBUILD TABLESPACE indexes_archive;

2. Function-Based Index Issues

-- Check function-based indexes that might need special handling
SELECT
  owner,
  index_name,
  table_name,
  column_expression,
  tablespace_name
FROM dba_ind_expressions
WHERE owner = 'SCHEMA'
ORDER BY index_name;

-- Rebuild function-based index
ALTER INDEX schema.function_based_idx REBUILD COMPUTE STATISTICS;

3. Bitmap Index Considerations

-- Check bitmap indexes (common in data warehouses)
SELECT
  owner,
  index_name,
  table_name,
  index_type,
  tablespace_name,
  compression
FROM dba_indexes
WHERE index_type = 'BITMAP'
  AND owner = 'SCHEMA';

-- Rebuild bitmap index with compression
ALTER INDEX schema.bitmap_idx REBUILD COMPRESS;

Related Errors

• ORA-01653 - Unable to extend table
• ORA-01655 - Unable to extend cluster
• ORA-01688 - Unable to extend table partition
• ORA-00959 - Tablespace does not exist

Quick Reference

Emergency Response Steps

1. ✓ Identify the failing index and its tablespace
2. ✓ Check tablespace space usage immediately
3. ✓ Add space to tablespace (resize or add datafile)
4. ✓ Retry the failed index operation
5. ✓ Verify index is created/rebuilt successfully
6. ✓ Implement monitoring for future prevention

Quick Commands

-- Check index tablespace usage
SELECT tablespace_name, ROUND(used_percent, 2) as used_pct
FROM dba_tablespace_usage_metrics
WHERE tablespace_name IN (SELECT DISTINCT tablespace_name FROM dba_indexes)
ORDER BY used_percent DESC;

-- Add space to index tablespace
ALTER TABLESPACE idx_tablespace ADD DATAFILE SIZE 2G AUTOEXTEND ON;

-- Rebuild index in different tablespace
ALTER INDEX schema.index_name REBUILD TABLESPACE new_tablespace ONLINE;

-- Check index size
SELECT ROUND(bytes/1024/1024, 2) as size_mb, extents
FROM dba_segments
WHERE owner = 'SCHEMA' AND segment_name = 'INDEX_NAME';

Index Sizing Guidelines

• B-tree indexes: ~10-15% of table size
• Bitmap indexes: Varies greatly, can be very small or very large
• Function-based indexes: Similar to B-tree plus function overhead
• Composite indexes: Larger due to multiple columns
• Unique indexes: Generally smaller due to no duplicate values