ORA-01545: Rollback Segment Specified Not Available
ORA-01545 occurs when Oracle cannot access a rollback segment that has been explicitly specified using the SET TRANSACTION USE ROLLBACK SEGMENT statement. This typically means the named segment is either offline, does not exist, or is unavailable in an Automatic Undo Management (AUM) environment. It is most common in legacy systems using Manual Undo Management, but can also appear in modern environments when outdated code is migrated without cleanup.
Top 3 Causes and Fixes
Cause 1: Rollback Segment is OFFLINE
The specified rollback segment exists but is currently in an OFFLINE state, often after a database restart where it was not included in the ROLLBACK_SEGMENTS initialization parameter.
-- Check current status of all rollback segments
SELECT segment_name, status, tablespace_name
FROM dba_rollback_segs
ORDER BY segment_name;
-- Bring the rollback segment ONLINE
ALTER ROLLBACK SEGMENT rbs01 ONLINE;
-- Persist across restarts via SPFILE
ALTER SYSTEM SET rollback_segments = 'RBS01', 'RBS02' SCOPE=SPFILE;
Cause 2: Rollback Segment Does Not Exist
The segment name specified in the application or script does not exist in the database — common after migrations, environment clones, or rollback segment reorganization.
-- Verify existing rollback segments
SELECT segment_name, status
FROM dba_rollback_segs
WHERE segment_name != 'SYSTEM';
-- Create a missing rollback segment if needed (Manual Undo only)
CREATE ROLLBACK SEGMENT rbs01
TABLESPACE rbs_tbs
STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 2 MAXEXTENTS UNLIMITED OPTIMAL 5M);
ALTER ROLLBACK SEGMENT rbs01 ONLINE;
Cause 3: AUM Environment with Legacy Manual Undo Code
In Oracle 9i and above with UNDO_MANAGEMENT=AUTO, manually specifying a rollback segment is not supported and will cause this error.
-- Confirm AUM is enabled
SELECT name, value
FROM v$parameter
WHERE name IN ('undo_management', 'undo_tablespace', 'undo_retention');
-- Remove or comment out legacy statements like:
-- SET TRANSACTION USE ROLLBACK SEGMENT rbs01; -- NOT supported in AUM
-- Tune undo retention instead
ALTER SYSTEM SET undo_retention = 900;
-- Check undo tablespace health
SELECT tablespace_name, status, contents
FROM dba_tablespaces
WHERE contents = 'UNDO';
Quick Fix Summary
| Scenario | Fix |
|---|---|
| Segment is OFFLINE | ALTER ROLLBACK SEGMENT <name> ONLINE; |
| Segment missing | Create it or update the script with a valid name |
| AUM environment | Remove SET TRANSACTION USE ROLLBACK SEGMENT from code |
Prevention Tips
1. Migrate to Automatic Undo Management (AUM)
Always use UNDO_MANAGEMENT=AUTO with a dedicated undo tablespace in Oracle 9i and above. This eliminates the need to manually manage rollback segments entirely and prevents ORA-01545 at the root level.
-- Create dedicated undo tablespace
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/oradata/undotbs2_01.dbf' SIZE 2G AUTOEXTEND ON;
ALTER SYSTEM SET undo_management = AUTO SCOPE=SPFILE;
ALTER SYSTEM SET undo_tablespace = UNDOTBS2 SCOPE=SPFILE;
2. Schedule Regular Segment Status Monitoring
Run a monitoring query on a schedule (via Oracle Scheduler or cron) to detect offline segments before they cause application errors. Alert immediately if any non-SYSTEM rollback segment is found in a non-ONLINE state.
-- Health check query to schedule
SELECT segment_name, status, tablespace_name,
ROUND(bytes/1024/1024, 2) AS size_mb
FROM dba_rollback_segs
WHERE status != 'ONLINE'
AND segment_name != 'SYSTEM';
Related Errors
- ORA-01552 – Cannot use system rollback segment for non-system tablespace
- ORA-30012 – Undo tablespace cannot be dropped or taken offline
- ORA-01628 – Max extents reached for rollback segment
📖 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)