DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01562 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01562: Failed to Extend Rollback Segment Number — Causes, Fixes & Prevention

ORA-01562 is thrown when Oracle cannot extend a rollback (undo) segment because there is insufficient space available in the Undo Tablespace. This error immediately rolls back the current transaction, making it one of the most disruptive errors in a production Oracle environment. It typically surfaces during large-scale DML operations such as bulk INSERTs, mass UPDATEs, or full-table DELETEs.

Top 3 Causes

1. Undo Tablespace Running Out of Space

This is the most common cause. When the datafile backing your Undo Tablespace reaches its size limit and AUTOEXTEND is either OFF or capped at MAXSIZE, Oracle has nowhere to write new undo entries.

-- Check current Undo Tablespace datafile status
SELECT 
    file_name,
    bytes / 1024 / 1024        AS size_mb,
    autoextensible,
    maxbytes / 1024 / 1024     AS max_size_mb
FROM dba_data_files
WHERE tablespace_name = 'UNDOTBS1';

-- Check active undo usage
SELECT 
    s.username,
    t.used_ublk * 8 / 1024 AS undo_used_mb,
    t.start_time
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
ORDER BY t.used_ublk DESC;
Enter fullscreen mode Exit fullscreen mode

2. MAXEXTENTS Limit Reached on Manual Rollback Segments

In legacy environments (pre-Oracle 9i) or systems still using manual undo management, each rollback segment has a hard MAXEXTENTS limit. Once that ceiling is hit, the segment cannot grow further.

-- Check rollback segment configuration
SELECT 
    segment_name,
    max_extents,
    extents,
    status
FROM dba_rollback_segs;

-- Raise or remove the extents limit
ALTER ROLLBACK SEGMENT rbs01 
STORAGE (MAXEXTENTS UNLIMITED);
Enter fullscreen mode Exit fullscreen mode

3. UNDO_RETENTION Too High Relative to Tablespace Size

When UNDO_RETENTION is set high, Oracle preserves committed undo blocks for that duration. If RETENTION GUARANTEE is also enabled, Oracle will refuse to overwrite retained undo even for active transactions — leading directly to ORA-01562.

-- Check current undo parameters
SHOW PARAMETER UNDO;

-- Lower retention if space is critical
ALTER SYSTEM SET UNDO_RETENTION = 300 SCOPE=BOTH;

-- Remove the hard guarantee so active transactions take priority
ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- Option 1: Resize the existing datafile
ALTER DATABASE DATAFILE '/oradata/ORCL/undotbs01.dbf' RESIZE 4096M;

-- Option 2: Enable AUTOEXTEND on the existing datafile
ALTER DATABASE DATAFILE '/oradata/ORCL/undotbs01.dbf'
AUTOEXTEND ON NEXT 512M MAXSIZE 10240M;

-- Option 3: Add a second datafile to the Undo Tablespace
ALTER TABLESPACE UNDOTBS1
ADD DATAFILE '/oradata/ORCL/undotbs02.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 512M MAXSIZE 10240M;

-- Option 4: Switch to a larger Undo Tablespace
CREATE UNDO TABLESPACE UNDOTBS2
DATAFILE '/oradata/ORCL/undotbs2_01.dbf' SIZE 4096M
AUTOEXTEND ON NEXT 512M MAXSIZE 20480M;

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2 SCOPE=BOTH;
Enter fullscreen mode Exit fullscreen mode

For large batch jobs, break the work into smaller chunks with periodic COMMITs to release undo space incrementally:

BEGIN
    LOOP
        DELETE FROM large_table
        WHERE status = 'INACTIVE' AND ROWNUM <= 10000;
        EXIT WHEN SQL%ROWCOUNT = 0;
        COMMIT;
    END LOOP;
END;
/
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Monitor Undo Tablespace utilization proactively. Schedule a monitoring job that alerts you when usage exceeds 70–80%. Enable AUTOEXTEND with a sensible MAXSIZE to handle unexpected spikes without manual intervention. 
-- Quick utilization check
SELECT 
    d.tablespace_name,
    ROUND(NVL(f.free_mb, 0), 2)            AS free_mb,
    ROUND(d.total_mb, 2)                   AS total_mb,
    ROUND((1 - NVL(f.free_mb,0)/d.total_mb)*100, 2) AS used_pct
FROM 
    (SELECT tablespace_name, SUM(bytes)/1024/1024 total_mb 
     FROM dba_data_files WHERE tablespace_name LIKE 'UNDO%' 
     GROUP BY tablespace_name) d
LEFT JOIN 
    (SELECT tablespace_name, SUM(bytes)/1024/1024 free_mb 
     FROM dba_free_space WHERE tablespace_name LIKE 'UNDO%' 
     GROUP BY tablespace_name) f
ON d.tablespace_name = f.tablespace_name;
Enter fullscreen mode Exit fullscreen mode
  1. Design batch processes with bounded transactions. Always commit every 5,000–10,000 rows during bulk DML. This not only prevents ORA-01562 but also reduces the risk of ORA-01555 (Snapshot Too Old) by keeping individual transactions short and predictable.

Related Errors

Error Code Description
ORA-01555 Snapshot too old — undo data overwritten before consistent read could complete
ORA-30036 Unable to extend segment in Undo Tablespace (AUM equivalent of ORA-01562)
ORA-01650 Unable to extend rollback segment in a specific tablespace

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)