ORA-30036: Unable to Extend Segment by N in Undo Tablespace

ORA-30036: Unable to Extend Segment by N in Undo Tablespace

Error Description

ORA-30036 occurs when Oracle cannot extend an undo segment in the undo tablespace due to insufficient space. This error prevents transactions from proceeding and can cause application failures and database hanging.

Complete Error Message

ORA-30036: unable to extend segment by [N] in undo tablespace '[tablespace_name]'

Where:

• N = Number of blocks Oracle tried to extend
• tablespace_name = Name of the undo tablespace

Severity Level

🔴 CRITICAL - Can halt transactions and cause database unavailability.

Common Causes

1. Insufficient Undo Tablespace Space

• Undo tablespace full or nearly full
• No autoextend configured on undo datafiles
• Fixed-size undo tablespace too small

2. Long-Running Transactions

• Large bulk operations consuming excessive undo
• Transactions not properly committed
• Import/export operations with large datasets

3. High Transaction Volume

• Concurrent transactions generating excessive undo
• Application design issues causing undo pressure
• Inadequate undo retention configuration

4. Configuration Issues

• UNDO_RETENTION set too high
• Insufficient undo tablespace sizing
• Autoextend limits reached

Immediate Diagnostic Steps

1. Check Undo Tablespace Usage

-- Check current undo 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_mb/total_mb)*100, 2) as pct_used
FROM (
  SELECT
    tablespace_name,
    SUM(bytes)/1024/1024 as total_mb,
    SUM(bytes - NVL(free_bytes, 0))/1024/1024 as used_mb,
    SUM(NVL(free_bytes, 0))/1024/1024 as free_mb
  FROM (
    SELECT
      tablespace_name,
      file_id,
      bytes,
      NULL as free_bytes
    FROM dba_data_files
    WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace')
    UNION ALL
    SELECT
      tablespace_name,
      file_id,
      NULL as bytes,
      SUM(bytes) as free_bytes
    FROM dba_free_space
    WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace')
    GROUP BY tablespace_name, file_id
  )
  GROUP BY tablespace_name
);

-- Check undo segment usage
SELECT
  segment_name,
  tablespace_name,
  ROUND(bytes/1024/1024, 2) as size_mb,
  status,
  extent_id
FROM dba_undo_extents
WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace')
ORDER BY bytes DESC;

2. Check Undo Configuration

-- Check current undo configuration
SELECT name, value
FROM v$parameter
WHERE name IN ('undo_management', 'undo_tablespace', 'undo_retention');

-- Check undo advisor recommendations
SELECT
  begin_time,
  end_time,
  undo_size,
  undo_size/1024/1024 as undo_size_mb,
  longest_query,
  required_retention
FROM v$undostat
WHERE begin_time >= SYSDATE - 1
ORDER BY begin_time DESC;

3. Identify Problem Transactions

-- Check active long-running transactions
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.machine,
  r.name as rollback_segment,
  t.used_ublk * 8192/1024/1024 as undo_mb,
  t.start_time,
  ROUND((SYSDATE - t.start_time) * 24 * 60, 2) as runtime_minutes
FROM v$transaction t,
     v$session s,
     v$rollname r
WHERE t.ses_addr = s.saddr
  AND t.xidusn = r.usn
ORDER BY t.used_ublk DESC;

-- Check undo generation rate
SELECT
  TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI') as time_window,
  undoblks * 8192/1024/1024 as undo_generated_mb,
  maxquerylen as max_query_seconds
FROM v$undostat
WHERE begin_time >= SYSDATE - 1/24  -- Last hour
ORDER BY begin_time DESC;

Immediate Solutions

1. Add Space to Undo Tablespace

Add New Datafile

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

-- Verify the addition
SELECT file_name, bytes/1024/1024 as size_mb, autoextensible, maxbytes/1024/1024 as max_mb
FROM dba_data_files
WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace');

Resize Existing Datafile

-- Resize existing undo datafile
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/undotbs01.dbf'
RESIZE 2G;

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

2. Kill Long-Running Problematic Transactions

-- Identify sessions to kill (use with caution)
SELECT
  'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' as kill_command,
  s.username,
  s.program,
  t.used_ublk * 8192/1024/1024 as undo_mb,
  ROUND((SYSDATE - t.start_time) * 24 * 60, 2) as runtime_minutes
FROM v$transaction t,
     v$session s
WHERE t.ses_addr = s.saddr
  AND t.used_ublk > 10000  -- Using more than ~80MB undo
  AND (SYSDATE - t.start_time) * 24 * 60 > 30  -- Running longer than 30 minutes
ORDER BY t.used_ublk DESC;

-- Execute kill command for problematic sessions (review first!)
-- ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

3. Temporary Undo Retention Reduction

-- Temporarily reduce undo retention to free space
SELECT value as current_undo_retention
FROM v$parameter
WHERE name = 'undo_retention';

-- Reduce retention temporarily (in seconds)
ALTER SYSTEM SET undo_retention = 900;  -- 15 minutes

-- Check if this helps free up space
SELECT
  status,
  ROUND(SUM(bytes)/1024/1024, 2) as total_mb
FROM dba_undo_extents
WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace')
GROUP BY status;

Long-term Solutions

1. Proper Undo Tablespace Sizing

-- Calculate recommended undo tablespace size
SELECT
  'Recommended Undo Size: ' ||
  ROUND(
    (SELECT MAX(undoblks * 8192) FROM v$undostat WHERE begin_time >= SYSDATE - 7) +
    (SELECT value FROM v$parameter WHERE name = 'undo_retention') *
    (SELECT MAX(undoblks * 8192 / (end_time - begin_time) / 86400) FROM v$undostat WHERE begin_time >= SYSDATE - 7)
  )/1024/1024, 2) || ' MB' as recommendation
FROM dual;

-- More comprehensive sizing query
WITH undo_stats AS (
  SELECT
    MAX(undoblks * 8192) as max_undo_bytes,
    AVG(undoblks * 8192) as avg_undo_bytes,
    MAX(maxquerylen) as max_query_len
  FROM v$undostat
  WHERE begin_time >= SYSDATE - 7
),
retention_setting AS (
  SELECT TO_NUMBER(value) as undo_retention
  FROM v$parameter
  WHERE name = 'undo_retention'
)
SELECT
  ROUND(us.max_undo_bytes/1024/1024, 2) as max_undo_mb,
  ROUND(us.avg_undo_bytes/1024/1024, 2) as avg_undo_mb,
  us.max_query_len as max_query_seconds,
  rs.undo_retention as retention_seconds,
  ROUND((us.avg_undo_bytes + (rs.undo_retention * us.avg_undo_bytes/3600))/1024/1024, 2) as recommended_size_mb
FROM undo_stats us, retention_setting rs;

2. Create New Undo Tablespace

-- Create new larger undo tablespace
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/app/oracle/oradata/orcl/undotbs2_01.dbf' SIZE 2G
AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

-- Switch to new undo tablespace
ALTER SYSTEM SET undo_tablespace = undotbs2;

-- Wait for old tablespace to become empty, then drop
-- Check if old tablespace is empty
SELECT COUNT(*) as active_transactions
FROM v$transaction t, v$rollname r
WHERE t.xidusn = r.usn
  AND r.name IN (
    SELECT segment_name
    FROM dba_rollback_segs
    WHERE tablespace_name = 'UNDOTBS1'
  );

-- When count is 0, drop old tablespace
-- DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

3. Optimize Undo Retention

-- Analyze query patterns to set optimal retention
SELECT
  ROUND(MAX(maxquerylen)/60, 2) as max_query_minutes,
  ROUND(AVG(maxquerylen)/60, 2) as avg_query_minutes,
  ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY maxquerylen)/60, 2) as pct95_query_minutes
FROM v$undostat
WHERE begin_time >= SYSDATE - 7;

-- Set appropriate undo retention (in seconds)
-- Rule of thumb: 1.5 * longest query time + buffer
ALTER SYSTEM SET undo_retention = 3600;  -- 1 hour

-- For critical systems with long queries
ALTER SYSTEM SET undo_retention = 7200;  -- 2 hours

Monitoring and Prevention

1. Create Undo Space Monitoring

-- Create undo monitoring view
CREATE OR REPLACE VIEW v_undo_space_monitor AS
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_mb/total_mb)*100, 2) as pct_used,
  CASE
    WHEN (used_mb/total_mb)*100 > 95 THEN 'CRITICAL'
    WHEN (used_mb/total_mb)*100 > 85 THEN 'WARNING'
    ELSE 'OK'
  END as status
FROM (
  SELECT
    tablespace_name,
    SUM(bytes)/1024/1024 as total_mb,
    SUM(bytes - NVL(free_bytes, 0))/1024/1024 as used_mb,
    SUM(NVL(free_bytes, 0))/1024/1024 as free_mb
  FROM (
    SELECT
      tablespace_name,
      file_id,
      bytes,
      NULL as free_bytes
    FROM dba_data_files
    WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace')
    UNION ALL
    SELECT
      tablespace_name,
      file_id,
      NULL as bytes,
      SUM(bytes) as free_bytes
    FROM dba_free_space
    WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace')
    GROUP BY tablespace_name, file_id
  )
  GROUP BY tablespace_name
);

-- Check undo space status
SELECT * FROM v_undo_space_monitor;

2. Undo Usage Alerting

-- Create alerting procedure
CREATE OR REPLACE PROCEDURE check_undo_space AS
  v_pct_used NUMBER;
  v_tablespace VARCHAR2(30);
BEGIN
  SELECT tablespace_name, pct_used
  INTO v_tablespace, v_pct_used
  FROM v_undo_space_monitor;

  IF v_pct_used > 90 THEN
    RAISE_APPLICATION_ERROR(-20001,
      'CRITICAL: Undo tablespace ' || v_tablespace || ' is ' || v_pct_used || '% full');
  ELSIF v_pct_used > 80 THEN
    -- Log warning
    INSERT INTO undo_alerts (alert_time, tablespace_name, pct_used, alert_level)
    VALUES (SYSDATE, v_tablespace, v_pct_used, 'WARNING');
    COMMIT;
  END IF;
END;
/

-- Schedule regular checks
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'undo_space_check',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN check_undo_space; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
    enabled         => TRUE
  );
END;
/

3. Transaction Monitoring

-- Create view for transaction monitoring
CREATE OR REPLACE VIEW v_long_transactions AS
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.machine,
  r.name as rollback_segment,
  t.used_ublk * 8192/1024/1024 as undo_mb,
  t.start_time,
  ROUND((SYSDATE - t.start_time) * 24 * 60, 2) as runtime_minutes,
  CASE
    WHEN (SYSDATE - t.start_time) * 24 * 60 > 120 THEN 'CRITICAL'
    WHEN (SYSDATE - t.start_time) * 24 * 60 > 60 THEN 'WARNING'
    ELSE 'OK'
  END as status
FROM v$transaction t,
     v$session s,
     v$rollname r
WHERE t.ses_addr = s.saddr
  AND t.xidusn = r.usn
ORDER BY t.used_ublk DESC;

-- Monitor long transactions
SELECT * FROM v_long_transactions WHERE status != 'OK';

Performance Optimization

1. Application-Level Improvements

-- Analyze transaction patterns
SELECT
  TO_CHAR(begin_time, 'YYYY-MM-DD HH24') as hour,
  ROUND(AVG(undoblks * 8192)/1024/1024, 2) as avg_undo_mb,
  ROUND(MAX(undoblks * 8192)/1024/1024, 2) as max_undo_mb,
  COUNT(*) as transaction_count
FROM v$undostat
WHERE begin_time >= SYSDATE - 7
GROUP BY TO_CHAR(begin_time, 'YYYY-MM-DD HH24')
ORDER BY 1;

-- Identify peak usage periods
SELECT
  TO_CHAR(begin_time, 'HH24') as hour_of_day,
  ROUND(AVG(undoblks * 8192)/1024/1024, 2) as avg_undo_mb,
  ROUND(MAX(undoblks * 8192)/1024/1024, 2) as max_undo_mb
FROM v$undostat
WHERE begin_time >= SYSDATE - 7
GROUP BY TO_CHAR(begin_time, 'HH24')
ORDER BY 1;

2. Batch Processing Optimization

-- Example of commit frequency optimization
DECLARE
  CURSOR c_large_update IS
    SELECT id FROM large_table WHERE status = 'PENDING';

  v_counter NUMBER := 0;
BEGIN
  FOR rec IN c_large_update LOOP
    UPDATE large_table
    SET status = 'PROCESSED',
        last_updated = SYSDATE
    WHERE id = rec.id;

    v_counter := v_counter + 1;

    -- Commit every 1000 rows to manage undo usage
    IF MOD(v_counter, 1000) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;

  COMMIT; -- Final commit
END;
/

Related Oracle Errors

• ORA-01555: Snapshot Too Old - Related to undo retention
• ORA-01652: Unable to Extend Temp Segment - Temporary space issues
• ORA-01654: Unable to Extend Index - Index space issues
• ORA-00060: Deadlock Detected - Transaction conflicts

Best Practices

1. Size undo tablespace appropriately based on workload analysis
2. Configure autoextend with reasonable limits
3. Monitor undo usage patterns regularly
4. Implement proper commit strategies in applications
5. Set appropriate undo retention based on query requirements
6. Use dedicated undo tablespaces for different workloads if needed
7. Implement alerting for undo space usage
8. Regular housekeeping of long-running transactions

Emergency Recovery Steps

-- Emergency procedure when undo is critically full
-- 1. Identify and kill the largest transaction consumer
SELECT 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;'
FROM v$transaction t, v$session s
WHERE t.ses_addr = s.saddr
  AND t.used_ublk = (SELECT MAX(used_ublk) FROM v$transaction);

-- 2. Immediately add space
ALTER TABLESPACE undotbs1
ADD DATAFILE '/u01/app/oracle/oradata/orcl/undotbs_emergency.dbf'
SIZE 1G AUTOEXTEND ON;

-- 3. Temporarily reduce retention
ALTER SYSTEM SET undo_retention = 300;  -- 5 minutes

-- 4. Monitor recovery
SELECT status, COUNT(*), ROUND(SUM(bytes)/1024/1024, 2) as mb
FROM dba_undo_extents
WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace')
GROUP BY status;

This error requires immediate attention as it can halt all DML operations in the database. Always have monitoring in place to prevent this situation.