ORA-01653 Unable to Extend Table - Tablespace Management

ORA-01653: Unable to Extend Table

Error Overview

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

This error occurs when Oracle cannot allocate additional space for a table because the tablespace is full or cannot extend. It’s one of the most common space management errors and can cause applications to fail when trying to insert data or perform operations that require additional space.

Understanding the Error

Space Allocation Process

INSERT/UPDATE Operation
       ↓
Table Needs More Space
       ↓
Request Extent from Tablespace
       ↓
Check Available Space
├── Free Space Available → Allocate Extent ✓
└── No Free Space → ORA-01653 Error ✗

Related Error Variants

• ORA-01654 - Unable to extend index
• ORA-01655 - Unable to extend cluster
• ORA-01656 - Unable to extend rollback segment
• ORA-01688 - Unable to extend table partition

Diagnostic Steps

1. Identify the Problem Tablespace

-- Check tablespace space 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
  GROUP BY df.tablespace_name
)
ORDER BY used_pct DESC;

-- Check specific tablespace details
SELECT
  file_name,
  bytes/1024/1024 as size_mb,
  maxbytes/1024/1024 as max_size_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 = 'USERS'  -- Replace with your tablespace
ORDER BY file_id;

2. Analyze the Affected Object

-- Find the table mentioned in the error
SELECT
  owner,
  table_name,
  tablespace_name,
  num_rows,
  ROUND(avg_row_len) as avg_row_len,
  ROUND((num_rows * avg_row_len)/1024/1024, 2) as estimated_size_mb,
  last_analyzed
FROM dba_tables
WHERE owner = 'SCHEMA_NAME'  -- Replace with actual schema
  AND table_name = 'TABLE_NAME';  -- Replace with actual table

-- Check table segments and extents
SELECT
  segment_name,
  segment_type,
  ROUND(bytes/1024/1024, 2) as size_mb,
  extents,
  max_extents,
  next_extent/1024/1024 as next_extent_mb,
  pct_increase
FROM dba_segments
WHERE owner = 'SCHEMA_NAME'
  AND segment_name = 'TABLE_NAME';

-- Check largest free extent in tablespace
SELECT
  tablespace_name,
  ROUND(MAX(bytes)/1024/1024, 2) as largest_free_mb,
  COUNT(*) as free_chunks
FROM dba_free_space
WHERE tablespace_name = 'USERS'  -- Replace with your tablespace
GROUP BY tablespace_name;

3. Check Current Operations

-- Check for long-running operations that might be consuming space
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
FROM v$session_longops
WHERE time_remaining > 0;

-- Check active transactions and undo usage
SELECT
  s.sid,
  s.serial#,
  s.username,
  t.used_ublk * (SELECT value FROM v$parameter WHERE name = 'db_block_size') / 1024 as undo_kb,
  t.start_time
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY t.used_ublk DESC;

Immediate Solutions

Solution 1: Add More Space to Tablespace

Add New Datafile

-- Add new datafile to tablespace
ALTER TABLESPACE users
ADD DATAFILE '/u01/app/oracle/oradata/orcl/users02.dbf'
SIZE 1G
AUTOEXTEND ON
NEXT 100M
MAXSIZE 10G;

-- For Oracle Managed Files (OMF)
ALTER TABLESPACE users
ADD DATAFILE
SIZE 1G
AUTOEXTEND ON
NEXT 100M
MAXSIZE 10G;

-- Verify the new datafile
SELECT file_name, bytes/1024/1024 as size_mb, autoextensible
FROM dba_data_files
WHERE tablespace_name = 'USERS'
ORDER BY file_id;

Resize Existing Datafile

-- Check current size and autoextend settings
SELECT
  file_name,
  bytes/1024/1024 as current_size_mb,
  maxbytes/1024/1024 as max_size_mb,
  autoextensible
FROM dba_data_files
WHERE tablespace_name = 'USERS';

-- Resize datafile manually
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf'
RESIZE 2G;

-- Enable autoextend if not already enabled
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf'
AUTOEXTEND ON
NEXT 100M
MAXSIZE 10G;

Solution 2: Free Up Space in Tablespace

Identify Space-Consuming Objects

-- Find largest objects in the tablespace
SELECT
  owner,
  segment_name,
  segment_type,
  ROUND(bytes/1024/1024, 2) as size_mb,
  extents
FROM dba_segments
WHERE tablespace_name = 'USERS'
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

-- Find tables that can be truncated or purged
SELECT
  owner,
  table_name,
  num_rows,
  ROUND((num_rows * avg_row_len)/1024/1024, 2) as estimated_size_mb,
  last_analyzed,
  temporary
FROM dba_tables
WHERE tablespace_name = 'USERS'
  AND (temporary = 'Y' OR table_name LIKE '%TEMP%' OR table_name LIKE '%LOG%')
ORDER BY estimated_size_mb DESC;

Clean Up Unnecessary Data

-- Truncate temporary tables (be very careful!)
TRUNCATE TABLE schema.temp_table;

-- Drop unused indexes
DROP INDEX schema.unused_index;

-- Purge recyclebin
PURGE RECYCLEBIN;

-- For specific user
PURGE USER_RECYCLEBIN;

-- Shrink tables to free up space
ALTER TABLE schema.large_table ENABLE ROW MOVEMENT;
ALTER TABLE schema.large_table SHRINK SPACE;
ALTER TABLE schema.large_table DISABLE ROW MOVEMENT;

-- Shrink table and indexes
ALTER TABLE schema.large_table SHRINK SPACE CASCADE;

Solution 3: Move Objects to Different Tablespace

Move Table to Different Tablespace

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

-- Move table to different tablespace
ALTER TABLE schema.large_table MOVE TABLESPACE users2;

-- Rebuild indexes after table move
SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD;'
FROM dba_indexes
WHERE table_owner = 'SCHEMA'
  AND table_name = 'LARGE_TABLE';

-- Move partition to different tablespace
ALTER TABLE schema.partitioned_table
MOVE PARTITION partition_name TABLESPACE users2;

Long-Term Solutions

1. Implement Autoextend Strategy

-- Configure autoextend for all datafiles
SELECT
  'ALTER DATABASE DATAFILE ''' || file_name || ''' AUTOEXTEND ON NEXT 100M MAXSIZE 10G;' as cmd
FROM dba_data_files
WHERE autoextensible = 'NO'
  AND tablespace_name NOT IN ('SYSTEM', 'SYSAUX');

-- Set reasonable maxsize limits
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf'
AUTOEXTEND ON
NEXT 100M
MAXSIZE UNLIMITED;  -- Be careful with UNLIMITED

-- Better approach with specific limit
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf'
AUTOEXTEND ON
NEXT 100M
MAXSIZE 20G;  -- Set appropriate limit

2. Implement Monitoring and Alerting

-- Create tablespace monitoring table
CREATE TABLE dba_tablespace_alerts (
  alert_time TIMESTAMP DEFAULT SYSTIMESTAMP,
  tablespace_name VARCHAR2(30),
  used_pct NUMBER,
  free_mb NUMBER,
  alert_level VARCHAR2(20),
  action_taken VARCHAR2(500)
);

-- Monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_tablespace_usage AS
  CURSOR ts_cursor IS
    SELECT
      df.tablespace_name,
      ROUND(((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes)) * 100, 2) as used_pct,
      ROUND(NVL(SUM(fs.bytes)/1024/1024, 0), 2) as free_mb,
      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 NOT IN ('SYSTEM', 'SYSAUX')
    GROUP BY df.tablespace_name;

  v_alert_level VARCHAR2(20);
BEGIN
  FOR ts_rec IN ts_cursor LOOP
    -- Determine alert level
    IF ts_rec.used_pct >= 95 THEN
      v_alert_level := 'CRITICAL';
    ELSIF ts_rec.used_pct >= 85 THEN
      v_alert_level := 'WARNING';
    ELSIF ts_rec.used_pct >= 75 THEN
      v_alert_level := 'INFO';
    ELSE
      v_alert_level := 'OK';
    END IF;

    -- Log alerts for non-OK conditions
    IF v_alert_level != 'OK' THEN
      INSERT INTO dba_tablespace_alerts (
        tablespace_name, used_pct, free_mb, alert_level
      ) VALUES (
        ts_rec.tablespace_name, ts_rec.used_pct, ts_rec.free_mb, v_alert_level
      );

      -- Auto-extend if critical and not autoextensible
      IF v_alert_level = 'CRITICAL' AND ts_rec.autoextensible = 'NO' THEN
        BEGIN
          -- Add new datafile
          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 new 1GB autoextend datafile'
          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 monitoring job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'MONITOR_TABLESPACE_USAGE',
    job_type => 'STORED_PROCEDURE',
    job_action => 'monitor_tablespace_usage',
    repeat_interval => 'FREQ=HOURLY',
    enabled => TRUE
  );
END;
/

3. Implement Space Management Policies

-- Create procedure for automatic cleanup
CREATE OR REPLACE PROCEDURE auto_space_management AS
  v_free_pct NUMBER;
BEGIN
  -- Check each tablespace
  FOR ts IN (SELECT DISTINCT tablespace_name FROM dba_data_files
             WHERE tablespace_name NOT IN ('SYSTEM', 'SYSAUX')) LOOP

    -- Calculate free space percentage
    SELECT
      ROUND(NVL(SUM(fs.bytes), 0) / SUM(df.bytes) * 100, 2)
    INTO v_free_pct
    FROM dba_data_files df
    LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name
    WHERE df.tablespace_name = ts.tablespace_name;

    -- If free space < 10%, try to reclaim space
    IF v_free_pct < 10 THEN
      -- Shrink objects in the tablespace
      FOR obj IN (
        SELECT owner, segment_name, segment_type
        FROM dba_segments
        WHERE tablespace_name = ts.tablespace_name
          AND segment_type = 'TABLE'
          AND owner NOT IN ('SYS', 'SYSTEM')
        ORDER BY bytes DESC
        FETCH FIRST 5 ROWS ONLY
      ) LOOP
        BEGIN
          -- Enable row movement and shrink
          EXECUTE IMMEDIATE 'ALTER TABLE ' || obj.owner || '.' || obj.segment_name ||
                          ' ENABLE ROW MOVEMENT';
          EXECUTE IMMEDIATE 'ALTER TABLE ' || obj.owner || '.' || obj.segment_name ||
                          ' SHRINK SPACE';
          EXECUTE IMMEDIATE 'ALTER TABLE ' || obj.owner || '.' || obj.segment_name ||
                          ' DISABLE ROW MOVEMENT';
        EXCEPTION
          WHEN OTHERS THEN
            -- Log the error but continue
            NULL;
        END;
      END LOOP;

      -- Coalesce the tablespace
      BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLESPACE ' || ts.tablespace_name || ' COALESCE';
      EXCEPTION
        WHEN OTHERS THEN
          NULL;
      END;
    END IF;
  END LOOP;
END;
/

Prevention Strategies

1. Proper Initial Sizing

-- Calculate tablespace size requirements
WITH table_sizes AS (
  SELECT
    tablespace_name,
    SUM(bytes)/1024/1024 as current_size_mb,
    COUNT(*) as object_count
  FROM dba_segments
  WHERE tablespace_name = 'USERS'
  GROUP BY tablespace_name
),
growth_estimate AS (
  SELECT
    tablespace_name,
    current_size_mb,
    object_count,
    current_size_mb * 1.5 as recommended_size_mb  -- 50% growth buffer
  FROM table_sizes
)
SELECT
  tablespace_name,
  current_size_mb,
  recommended_size_mb,
  recommended_size_mb - current_size_mb as additional_space_needed_mb
FROM growth_estimate;

-- Create tablespace with proper sizing
CREATE TABLESPACE users_new
DATAFILE '/u01/app/oracle/oradata/orcl/users_new01.dbf' SIZE 2G,
         '/u01/app/oracle/oradata/orcl/users_new02.dbf' SIZE 2G
AUTOEXTEND ON NEXT 100M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;

2. Regular Maintenance

-- Weekly space analysis report
CREATE OR REPLACE PROCEDURE weekly_space_report AS
  v_report CLOB;
BEGIN
  v_report := 'TABLESPACE USAGE REPORT - ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD') || CHR(10) ||
              '=================================' || CHR(10) || CHR(10);

  FOR ts IN (
    SELECT
      df.tablespace_name,
      ROUND(SUM(df.bytes)/1024/1024, 2) as total_mb,
      ROUND((SUM(df.bytes) - NVL(SUM(fs.bytes), 0))/1024/1024, 2) as used_mb,
      ROUND(NVL(SUM(fs.bytes)/1024/1024, 0), 2) as free_mb,
      ROUND(((SUM(df.bytes) - NVL(SUM(fs.bytes), 0)) / SUM(df.bytes)) * 100, 2) as used_pct
    FROM dba_data_files df
    LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name
    GROUP BY df.tablespace_name
    ORDER BY used_pct DESC
  ) LOOP
    v_report := v_report ||
                'Tablespace: ' || ts.tablespace_name || CHR(10) ||
                '  Total: ' || ts.total_mb || ' MB' || CHR(10) ||
                '  Used: ' || ts.used_mb || ' MB (' || ts.used_pct || '%)' || CHR(10) ||
                '  Free: ' || ts.free_mb || ' MB' || CHR(10) || CHR(10);
  END LOOP;

  -- Output report (can be modified to send email)
  DBMS_OUTPUT.PUT_LINE(v_report);
END;
/

-- Schedule weekly report
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'WEEKLY_SPACE_REPORT',
    job_type => 'STORED_PROCEDURE',
    job_action => 'weekly_space_report',
    repeat_interval => 'FREQ=WEEKLY; BYDAY=MON; BYHOUR=9',
    enabled => TRUE
  );
END;
/

Related Errors

• ORA-01654 - Unable to extend index
• ORA-01655 - Unable to extend cluster
• ORA-01688 - Unable to extend table partition
• ORA-01652 - Unable to extend temp segment

Quick Reference

Emergency Response Steps

1. ✓ Identify the full tablespace from error message
2. ✓ Check tablespace space usage and autoextend settings
3. ✓ Add space immediately (resize or add datafile)
4. ✓ Complete the failed operation
5. ✓ Analyze space usage patterns
6. ✓ Implement long-term monitoring
7. ✓ Schedule regular maintenance

Quick Commands

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

-- Add space quickly
ALTER TABLESPACE users ADD DATAFILE SIZE 1G AUTOEXTEND ON;

-- Enable autoextend on existing files
ALTER DATABASE DATAFILE '/path/to/file.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

-- Find largest objects
SELECT owner, segment_name, ROUND(bytes/1024/1024) as mb
FROM dba_segments
WHERE tablespace_name = 'USERS'
ORDER BY bytes DESC
FETCH FIRST 10 ROWS ONLY;