ORA-01650 Unable to Extend Rollback Segment - Undo Management Guide

ORA-01650: Unable to Extend Rollback Segment by N in Tablespace

Error Overview

Error Text: ORA-01650: unable to extend rollback segment string by string in tablespace string

This error occurs in Oracle databases using manual undo management (rollback segments) when Oracle cannot extend a rollback segment to accommodate transaction undo data. While most modern Oracle databases use Automatic Undo Management (AUM), legacy systems and specific configurations may still encounter this error.

Understanding Rollback Segments

Manual vs Automatic Undo Management

Manual Undo (Legacy)

-- Manual undo management parameters
UNDO_MANAGEMENT = MANUAL
ROLLBACK_SEGMENTS = (RBS01, RBS02, RBS03, RBS04)

-- Rollback segments in SYSTEM tablespace and dedicated RBS tablespace

Automatic Undo (Modern)

-- Automatic undo management (recommended)
UNDO_MANAGEMENT = AUTO
UNDO_TABLESPACE = UNDOTBS1
UNDO_RETENTION = 3600

Why ORA-01650 Occurs

• Transaction needs more undo space than available
• Rollback segment cannot allocate additional extents
• RBS tablespace is full or highly fragmented
• Storage parameters prevent extension

Root Causes

1. Rollback Segment Tablespace Full

• Dedicated RBS tablespace has no free space
• All datafiles at maximum size
• No autoextend enabled on datafiles

2. Large Transaction Activity

• Long-running transactions requiring extensive undo
• Bulk operations without periodic commits
• Multiple large concurrent transactions

3. Storage Parameter Limitations

• MAXEXTENTS reached for rollback segment
• NEXT extent size too large for available space
• PCTINCREASE causing exponential growth

4. Fragmentation Issues

• RBS tablespace heavily fragmented
• Cannot find contiguous space for next extent
• Many small free chunks but no large ones

Diagnostic Queries

Check Current Undo Management Mode

-- Determine if using manual or automatic undo
SHOW PARAMETER undo_management;

-- If MANUAL, check rollback segments
SELECT segment_name, tablespace_name, status,
       initial_extent/1024 as initial_kb,
       next_extent/1024 as next_kb,
       max_extents, extents
FROM dba_rollback_segs
WHERE segment_name != 'SYSTEM'
ORDER BY segment_name;

-- Check which rollback segments are online
SELECT segment_name, status, instance_num
FROM v$rollstat r, dba_rollback_segs d
WHERE r.usn = d.segment_id;

Analyze RBS Tablespace Usage

-- Check rollback segment tablespace space
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), 2) as used_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
  WHERE tablespace_name IN (
    SELECT DISTINCT tablespace_name
    FROM dba_rollback_segs
    WHERE segment_name != 'SYSTEM'
  )
  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;

-- Check fragmentation in RBS tablespace
SELECT tablespace_name,
       COUNT(*) as free_chunks,
       ROUND(MAX(bytes)/1024/1024, 2) as largest_free_mb,
       ROUND(MIN(bytes)/1024/1024, 2) as smallest_free_mb,
       ROUND(AVG(bytes)/1024/1024, 2) as avg_free_mb
FROM dba_free_space
WHERE tablespace_name = 'RBS'
GROUP BY tablespace_name;

Check Transaction Activity

-- Current rollback segment usage
SELECT r.name, s.sid, s.serial#, s.username, s.status,
       r.rssize/1024 as rbs_size_kb,
       r.hwmsize/1024 as hwm_size_kb,
       r.gets, r.waits
FROM v$rollstat r, v$rollname n, v$session s
WHERE r.usn = n.usn(+)
  AND r.usn = s.audsid(+)
  AND r.rssize > 0
ORDER BY r.rssize DESC;

-- Find large transactions
SELECT s.sid, s.serial#, s.username,
       t.used_ublk * 8192 / 1024 as undo_kb,
       t.used_urec as undo_records,
       s.sql_id, s.program
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr
  AND t.used_ublk > 1000  -- More than 1000 undo blocks
ORDER BY t.used_ublk DESC;

Resolution Strategies

1. Add Space to RBS Tablespace

Add New Datafile

-- Add datafile to rollback segment tablespace
ALTER TABLESPACE rbs
ADD DATAFILE '/u01/oradata/prod/rbs_02.dbf'
SIZE 2G AUTOEXTEND ON MAXSIZE 10G;

-- Check existing datafiles first
SELECT file_name, tablespace_name,
       ROUND(bytes/1024/1024, 2) as size_mb,
       ROUND(maxbytes/1024/1024, 2) as max_size_mb,
       autoextensible
FROM dba_data_files
WHERE tablespace_name = 'RBS';

Resize Existing Datafiles

-- Resize existing datafile
ALTER DATABASE DATAFILE '/u01/oradata/prod/rbs_01.dbf'
RESIZE 5G;

-- Enable autoextend
ALTER DATABASE DATAFILE '/u01/oradata/prod/rbs_01.dbf'
AUTOEXTEND ON MAXSIZE 10G;

2. Create Additional Rollback Segments

Add New Rollback Segment

-- Create new rollback segment
CREATE ROLLBACK SEGMENT rbs05
TABLESPACE rbs
STORAGE (
  INITIAL 10M
  NEXT 10M
  MINEXTENTS 2
  MAXEXTENTS UNLIMITED
  PCTINCREASE 0
);

-- Bring it online
ALTER ROLLBACK SEGMENT rbs05 ONLINE;

-- Update init parameter (for restart)
-- Add RBS05 to ROLLBACK_SEGMENTS parameter

Optimal Rollback Segment Sizing

-- Size rollback segments based on workload
CREATE ROLLBACK SEGMENT rbs_large
TABLESPACE rbs
STORAGE (
  INITIAL 50M      -- Large initial for big transactions
  NEXT 50M         -- Consistent extent size
  MINEXTENTS 4     -- Minimum extents to keep online
  MAXEXTENTS 100   -- Allow growth but not unlimited
  PCTINCREASE 0    -- No exponential growth
  OPTIMAL 200M     -- Shrink back to this size when possible
);

3. Migrate to Automatic Undo Management

Migration Steps

-- Step 1: Create undo tablespace
CREATE UNDO TABLESPACE undotbs1
DATAFILE '/u01/oradata/prod/undotbs1_01.dbf'
SIZE 2G AUTOEXTEND ON MAXSIZE 32G;

-- Step 2: Update parameters (requires restart)
-- In parameter file:
-- UNDO_MANAGEMENT = AUTO
-- UNDO_TABLESPACE = UNDOTBS1
-- UNDO_RETENTION = 3600
-- Remove ROLLBACK_SEGMENTS parameter

-- Step 3: Restart database
SHUTDOWN IMMEDIATE;
STARTUP;

-- Step 4: Drop rollback segments after migration
ALTER ROLLBACK SEGMENT rbs01 OFFLINE;
DROP ROLLBACK SEGMENT rbs01;
-- Repeat for all manual rollback segments

-- Step 5: Drop RBS tablespace
DROP TABLESPACE rbs INCLUDING CONTENTS AND DATAFILES;

4. Immediate Workarounds

Take Rollback Segments Offline/Online

-- Cycle problematic rollback segment
ALTER ROLLBACK SEGMENT rbs02 OFFLINE;
-- Wait for pending transactions to complete
SELECT COUNT(*) FROM v$transaction WHERE xidusn = (
  SELECT segment_id FROM dba_rollback_segs WHERE segment_name = 'RBS02'
);
-- When count = 0, bring back online
ALTER ROLLBACK SEGMENT rbs02 ONLINE;

Force Transaction Commit/Rollback

-- Find and kill problematic sessions (use carefully)
SELECT s.sid, s.serial#, s.username, s.program,
       t.used_ublk * 8192 / 1024 / 1024 as undo_mb
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr
  AND t.used_ublk > 10000  -- Large transaction
ORDER BY t.used_ublk DESC;

-- Kill session (forces rollback)
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Prevention Strategies

1. Proper Sizing Formula

-- Calculate rollback segment size needs
-- Formula: RBS_SIZE = UPS × RT × # Concurrent Transactions
-- Where:
-- UPS = Undo Per Second
-- RT = Retention Time needed
--
-- Example calculation:
WITH undo_stats AS (
  SELECT AVG(undoblks * 8192) / AVG((end_time - begin_time) * 86400) as ups
  FROM v$undostat
  WHERE begin_time > SYSDATE - 7
)
SELECT
  ups,
  ups * 3600 / 1024 / 1024 as undo_needed_per_hour_mb,
  ups * 3600 * 4 / 1024 / 1024 as recommended_rbs_size_mb  -- 4 concurrent large txns
FROM undo_stats;

2. Monitoring Setup

-- Create rollback segment monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_rollback_segments AS
BEGIN
  -- Check RBS tablespace usage
  FOR rec IN (
    SELECT tablespace_name,
           ROUND((total_bytes - free_bytes) / total_bytes * 100, 2) as used_pct
    FROM (
      SELECT df.tablespace_name,
             SUM(df.bytes) as total_bytes,
             NVL(SUM(fs.bytes), 0) as free_bytes
      FROM dba_data_files df
      LEFT JOIN dba_free_space fs ON df.tablespace_name = fs.tablespace_name
      WHERE df.tablespace_name IN (
        SELECT DISTINCT tablespace_name
        FROM dba_rollback_segs
        WHERE segment_name != 'SYSTEM'
      )
      GROUP BY df.tablespace_name
    )
    WHERE (total_bytes - free_bytes) / total_bytes * 100 > 85
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('WARNING: RBS tablespace ' || rec.tablespace_name ||
                        ' is ' || rec.used_pct || '% full');
  END LOOP;

  -- Check for offline rollback segments
  FOR rec IN (
    SELECT segment_name FROM dba_rollback_segs
    WHERE status = 'OFFLINE' AND segment_name != 'SYSTEM'
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('WARNING: Rollback segment ' || rec.segment_name ||
                        ' is offline');
  END LOOP;
END;
/

3. Best Practices for Manual Undo

• Size rollback segments based on transaction patterns
• Use multiple rollback segments to distribute load
• Set OPTIMAL parameter to prevent excessive growth
• Monitor RBS tablespace usage regularly
• Plan migration to Automatic Undo Management

Migration Considerations

Why Migrate to AUM?

• Simplified Management: Oracle manages undo automatically
• Better Space Utilization: Dynamic space allocation
• Enhanced Features: Flashback queries, retention guarantees
• Reduced Errors: Eliminates ORA-01650 and related issues

Migration Checklist

-- Pre-migration checks
-- 1. Current RBS usage
SELECT SUM(bytes)/1024/1024 as total_rbs_mb
FROM dba_segments
WHERE segment_type = 'ROLLBACK';

-- 2. Peak undo requirements
SELECT MAX(undoblks) * 8192 / 1024 / 1024 as peak_undo_mb
FROM v$undostat;

-- 3. Application transaction patterns
SELECT s.program, COUNT(*),
       AVG(t.used_ublk * 8192 / 1024) as avg_undo_kb
FROM v$session s, v$transaction t
WHERE s.saddr = t.ses_addr
GROUP BY s.program;

Related Errors

• ORA-01555 - Snapshot too old (modern AUM equivalent)
• ORA-30036 - Unable to extend undo segment (AUM)
• ORA-01652 - Unable to extend temp segment
• ORA-01653 - Unable to extend table
• ORA-01658 - Unable to create initial extent

Emergency Response

Immediate Actions

1. Check RBS tablespace space

   SELECT tablespace_name,
          ROUND(SUM(bytes)/1024/1024, 2) as free_mb
   FROM dba_free_space
   WHERE tablespace_name = 'RBS';

2. Add emergency space

   ALTER TABLESPACE rbs
   ADD DATAFILE '/tmp/rbs_emergency.dbf' SIZE 1G;

3. Identify large transactions

   SELECT s.sid, s.serial#, s.username,
          t.used_ublk * 8192 / 1024 / 1024 as undo_mb
   FROM v$session s, v$transaction t
   WHERE s.saddr = t.ses_addr
   ORDER BY t.used_ublk DESC;

4. Consider killing large transactions

   -- Only if necessary and safe to do so
   ALTER SYSTEM KILL SESSION 'sid,serial#';

Long-term Solution

Plan migration to Automatic Undo Management to eliminate ORA-01650 errors permanently and benefit from Oracle’s improved undo management capabilities.