DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01534 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01534: rollback segment does not exist — Causes, Fixes & Prevention

ORA-01534 is thrown by Oracle when a session or initialization parameter explicitly references a rollback segment that does not exist in the database or is in an inaccessible state. This error is most common in Manual Undo Management (MUM) environments but can still surface in modern databases through legacy scripts or misconfigured parameters. Understanding the root cause quickly is critical, as it can block transactions or even prevent the database from starting.

Top 3 Causes

1. Referencing a Non-Existent Rollback Segment in Code

Hardcoded SET TRANSACTION USE ROLLBACK SEGMENT statements pointing to a segment that was never created — or was created under a different name — are the most frequent trigger.

-- This will raise ORA-01534 if 'rbs_old' does not exist
SET TRANSACTION USE ROLLBACK SEGMENT rbs_old;

-- Verify what rollback segments actually exist
SELECT segment_name, tablespace_name, status
FROM   dba_rollback_segs
ORDER  BY segment_name;
Enter fullscreen mode Exit fullscreen mode

2. Invalid Entry in the ROLLBACK_SEGMENTS Initialization Parameter

If init.ora or spfile lists a rollback segment name that doesn't physically exist, Oracle raises ORA-01534 at startup or when attempting to bring the segment online.

-- Check current parameter value
SHOW PARAMETER rollback_segments;

-- View all parameters related to undo/rollback
SELECT name, value
FROM   v$parameter
WHERE  name IN ('undo_management', 'undo_tablespace', 'rollback_segments');

-- Fix: remove invalid entry or switch to AUM
ALTER SYSTEM SET rollback_segments = 'RBS01' SCOPE = SPFILE;
-- After editing, restart the database to apply changes
Enter fullscreen mode Exit fullscreen mode

3. Rollback Segment Dropped or Left OFFLINE

A segment that existed at some point may have been dropped (DROP ROLLBACK SEGMENT) or taken offline, while references to it remain in application code or parameter files.

-- Check for OFFLINE rollback segments
SELECT segment_name, status
FROM   dba_rollback_segs
WHERE  status != 'ONLINE';

-- Bring an OFFLINE segment back online
ALTER ROLLBACK SEGMENT rbs01 ONLINE;

-- Or, if no longer needed, clean it up properly
ALTER ROLLBACK SEGMENT rbs01 OFFLINE;
DROP ROLLBACK SEGMENT rbs01;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Option A — Create the missing rollback segment (MUM environments)

-- Create the rollback segment and bring it online
CREATE ROLLBACK SEGMENT rbs01
  TABLESPACE rbs_tbs
  STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 2 MAXEXTENTS UNLIMITED OPTIMAL 10M);

ALTER ROLLBACK SEGMENT rbs01 ONLINE;
Enter fullscreen mode Exit fullscreen mode

Option B — Migrate to Automatic Undo Management (recommended)

-- Switch to AUM — eliminates manual rollback segment management entirely
ALTER SYSTEM SET undo_management  = 'AUTO'     SCOPE = SPFILE;
ALTER SYSTEM SET undo_tablespace  = 'UNDOTBS1' SCOPE = SPFILE;

-- Restart the database, then verify
SELECT name, value
FROM   v$parameter
WHERE  name IN ('undo_management', 'undo_tablespace');

-- Check undo tablespace health
SELECT tablespace_name, status, retention
FROM   dba_tablespaces
WHERE  contents = 'UNDO';
Enter fullscreen mode Exit fullscreen mode

Option C — Remove or comment out legacy SET TRANSACTION statements

-- Before (causes ORA-01534 in AUM or if segment missing):
-- SET TRANSACTION USE ROLLBACK SEGMENT rbs_old;

-- After (safe for AUM environments):
SET TRANSACTION READ WRITE;
-- Oracle automatically assigns an undo segment — no manual spec needed
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Adopt Automatic Undo Management (AUM). Oracle has recommended AUM since 9i. Set UNDO_MANAGEMENT=AUTO and let Oracle handle undo allocation. Audit all application code for SET TRANSACTION USE ROLLBACK SEGMENT statements and remove them — they are unnecessary and risky in AUM environments.

  2. Include rollback/undo segment validation in your DB health-check scripts. After any migration, recovery, or storage reconfiguration, run a quick query against DBA_ROLLBACK_SEGS and V$PARAMETER to confirm that every segment listed in ROLLBACK_SEGMENTS actually exists and is ONLINE. Automate this check and alert on discrepancies before they cause production incidents.

Related Errors

Error Code Description
ORA-01535 Rollback segment already exists (opposite of ORA-01534)
ORA-01552 Cannot use SYSTEM rollback segment for non-system tablespace
ORA-30012 Undo tablespace does not exist (AUM equivalent of ORA-01534)
ORA-01650 Unable to extend undo segment — related space management issue

📖 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)