ORA-01552: Cannot Use System Rollback Segment - Configure Undo Tablespace

ORA-01552: Cannot Use System Rollback Segment for Non-System Tablespace

Error Overview

Error Text: ORA-01552: cannot use system rollback segment for non-system tablespace 'string'

The ORA-01552 error occurs when a transaction modifies data in a non-SYSTEM tablespace but Oracle has no user-defined rollback segment or undo tablespace available to record undo for that transaction. Oracle falls back to the SYSTEM rollback segment in this situation, but the SYSTEM rollback segment is prohibited from servicing transactions against non-SYSTEM tablespaces as a protective measure. The result is a hard error that prevents the transaction from executing.

This error is most commonly seen in databases using manual undo management (pre-Automatic Undo Management) that have insufficient or misconfigured rollback segments, or in newly restored/created databases that have not yet had an undo tablespace properly configured.

Common Causes

1. No Undo Tablespace Configured (AUM Not Properly Set Up)

• Database is running with UNDO_MANAGEMENT = AUTO but UNDO_TABLESPACE is not set or points to a tablespace that does not exist
• A newly cloned or restored database was opened without first creating or pointing to a valid undo tablespace

2. Undo Tablespace Dropped or Inaccessible

• The undo tablespace was accidentally dropped
• The undo tablespace datafile is offline, making the tablespace unavailable for new transactions
• The database was restored to a point in time before the undo tablespace was created

3. Manual Undo Management with No Online Rollback Segments

• Database is running with UNDO_MANAGEMENT = MANUAL but all user-defined rollback segments are offline
• Rollback segments exist but none have been brought online (via ALTER ROLLBACK SEGMENT ... ONLINE or the ROLLBACK_SEGMENTS init parameter)
• The only segment available is the SYSTEM rollback segment, which cannot serve non-SYSTEM tablespaces

4. Migration from Manual to Automatic Undo Management Incomplete

• The UNDO_MANAGEMENT parameter was changed to AUTO in the spfile, but the database was restarted without having created an undo tablespace first
• An undo tablespace was created under a different name than what UNDO_TABLESPACE specifies

5. Database Opened in Restricted or Upgrade Mode

• Opening in MIGRATE or UPGRADE mode can limit undo availability
• A database opened for maintenance without the standard undo infrastructure

Diagnostic Queries

Check Current Undo Configuration

-- Review all undo-related initialization parameters
SELECT name, value, description
FROM v$parameter
WHERE name IN (
  'undo_management',
  'undo_tablespace',
  'undo_retention',
  'rollback_segments'
)
ORDER BY name;

-- Confirm what undo tablespace the database currently uses
SELECT
  tablespace_name,
  status,
  contents,
  retention,
  block_size
FROM dba_tablespaces
WHERE contents = 'UNDO'
ORDER BY tablespace_name;

Check Undo Tablespace Datafiles

-- Check datafiles belonging to undo tablespaces
SELECT
  d.tablespace_name,
  d.file_name,
  d.status,
  ROUND(d.bytes / 1024 / 1024, 2) AS size_mb,
  d.autoextensible,
  ROUND(d.maxbytes / 1024 / 1024 / 1024, 2) AS max_gb
FROM dba_data_files d
JOIN dba_tablespaces t ON d.tablespace_name = t.tablespace_name
WHERE t.contents = 'UNDO'
ORDER BY d.tablespace_name, d.file_name;

-- Also check v$datafile for online status
SELECT
  df.file#,
  df.name,
  df.status,
  ts.name AS tablespace_name
FROM v$datafile df
JOIN v$tablespace ts ON df.ts# = ts.ts#
WHERE ts.name IN (
  SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO'
);

Check Rollback Segments (Manual Undo Management)

-- View all rollback segments and their status
SELECT
  segment_name,
  owner,
  tablespace_name,
  status,
  initial_extent,
  next_extent,
  max_extents,
  min_extents
FROM dba_rollback_segs
ORDER BY status, segment_name;

-- Check v$rollname for currently online rollback segments
SELECT
  usn,
  name,
  xacts,
  rssize,
  writes,
  status
FROM v$rollstat r
JOIN v$rollname n ON r.usn = n.usn
ORDER BY usn;

Check Active Transactions and Their Undo Usage

-- Identify active transactions and which rollback/undo segment they use
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  t.used_ublk,
  t.used_urec,
  t.start_time,
  r.name           AS rollback_segment
FROM v$session s
JOIN v$transaction t ON s.taddr = t.addr
LEFT JOIN v$rollname r ON t.xidusn = r.usn
ORDER BY t.used_ublk DESC;

Check the Alert Log for Undo-Related Messages

-- Look for undo configuration errors at startup and runtime
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-01552%'
   OR message_text LIKE '%undo%'
   OR message_text LIKE '%rollback segment%'
   OR message_text LIKE '%UNDOTBS%'
ORDER BY originating_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

Step-by-Step Resolution

1. Create an Undo Tablespace (If None Exists)

The most common fix for a newly built or restored database running AUM:

-- Create a new undo tablespace
CREATE UNDO TABLESPACE undotbs1
  DATAFILE '/u01/oradata/orcl/undotbs01.dbf'
  SIZE 2G
  AUTOEXTEND ON NEXT 256M MAXSIZE 20G
  RETENTION NOGUARANTEE;

-- Set the database to use the new undo tablespace
ALTER SYSTEM SET undo_tablespace = undotbs1 SCOPE=BOTH;

-- Verify the change
SELECT name, value FROM v$parameter WHERE name = 'undo_tablespace';

2. Switch to an Existing Undo Tablespace

If an undo tablespace already exists but the parameter points to a non-existent or wrong tablespace:

-- Find existing undo tablespaces
SELECT tablespace_name, status FROM dba_tablespaces WHERE contents = 'UNDO';

-- Switch to the correct one
ALTER SYSTEM SET undo_tablespace = undotbs1 SCOPE=BOTH;

-- Immediately confirm the undo tablespace is active
SELECT value FROM v$parameter WHERE name = 'undo_tablespace';

-- Confirm it is ONLINE
SELECT tablespace_name, status FROM dba_tablespaces WHERE contents = 'UNDO';

3. Bring Offline Rollback Segments Online (Manual Undo Management)

If the database uses manual undo management and rollback segments are offline:

-- Bring individual rollback segments online
ALTER ROLLBACK SEGMENT rbs01 ONLINE;
ALTER ROLLBACK SEGMENT rbs02 ONLINE;
ALTER ROLLBACK SEGMENT rbs03 ONLINE;
ALTER ROLLBACK SEGMENT rbs04 ONLINE;

-- Confirm they are now online
SELECT segment_name, status FROM dba_rollback_segs WHERE segment_name != 'SYSTEM';

To make this permanent across restarts, add the segments to the init parameter:

-- In spfile: add rollback_segments parameter
ALTER SYSTEM SET rollback_segments = 'RBS01,RBS02,RBS03,RBS04' SCOPE=SPFILE;
-- Requires restart to persist

4. Migrate from Manual Undo Management to Automatic Undo Management

The recommended long-term resolution for any database still using manual rollback segments:

-- Step 1: Create the undo tablespace (if not already present)
CREATE UNDO TABLESPACE undotbs1
  DATAFILE '/u01/oradata/orcl/undotbs01.dbf'
  SIZE 4G AUTOEXTEND ON NEXT 512M MAXSIZE 32G;

-- Step 2: Set the undo management parameters in spfile
ALTER SYSTEM SET undo_management = AUTO SCOPE=SPFILE;
ALTER SYSTEM SET undo_tablespace = undotbs1 SCOPE=SPFILE;
-- Remove the old rollback_segments parameter if set
ALTER SYSTEM RESET rollback_segments SCOPE=SPFILE;

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

-- Step 4: After restart, verify AUM is active
SHOW PARAMETER undo_management;
SHOW PARAMETER undo_tablespace;

-- Step 5: Verify no active transactions on old rollback segments
SELECT segment_name, status, xacts FROM v$rollstat r
JOIN v$rollname n ON r.usn = n.usn
WHERE n.name NOT IN ('SYSTEM', '_SYSSMU%');

-- Step 6: Take old public rollback segments offline once drained
-- (Only after confirming no active transactions)
ALTER ROLLBACK SEGMENT rbs01 OFFLINE;

-- Step 7: Drop the old rollback segments and their tablespace
DROP ROLLBACK SEGMENT rbs01;
DROP ROLLBACK SEGMENT rbs02;
-- ... repeat for all manual segments

DROP TABLESPACE rbs_tablespace INCLUDING CONTENTS AND DATAFILES;

5. Repair an Undo Tablespace After Datafile Loss

If the undo tablespace datafile was lost and needs to be restored:

-- From RMAN: restore the undo tablespace
RUN {
  SQL 'ALTER TABLESPACE undotbs1 OFFLINE IMMEDIATE';
  RESTORE TABLESPACE undotbs1;
  RECOVER TABLESPACE undotbs1;
  SQL 'ALTER TABLESPACE undotbs1 ONLINE';
}

If no backup exists and undo transactions are not needed (no active transactions were in-flight):

-- Recreate the undo tablespace as an emergency measure
-- WARNING: Any in-flight transactions using the old undo tablespace will be lost
-- This is only appropriate if the database was shut cleanly

-- Drop the old broken undo tablespace reference
-- (Only if you cannot restore it and the database is in a clean state)
ALTER SYSTEM SET undo_tablespace = '' SCOPE=SPFILE;

-- Restart and create a new undo tablespace
CREATE UNDO TABLESPACE undotbs_new
  DATAFILE '/u01/oradata/orcl/undotbs_new01.dbf'
  SIZE 4G AUTOEXTEND ON;

ALTER SYSTEM SET undo_tablespace = undotbs_new SCOPE=BOTH;

Prevention Strategies

1. Monitor Undo Tablespace Health

-- Create a procedure to monitor undo tablespace availability and usage
CREATE OR REPLACE PROCEDURE monitor_undo_tablespace AS
  v_undo_ts     VARCHAR2(30);
  v_ts_status   VARCHAR2(10);
  v_used_pct    NUMBER;
BEGIN
  -- Get current undo tablespace
  SELECT value INTO v_undo_ts FROM v$parameter WHERE name = 'undo_tablespace';

  -- Check its status
  SELECT status INTO v_ts_status
  FROM dba_tablespaces
  WHERE tablespace_name = v_undo_ts;

  -- Check space usage
  SELECT
    ROUND(
      (1 - NVL(SUM(f.bytes),0) / SUM(d.bytes)) * 100, 1
    ) INTO v_used_pct
  FROM dba_data_files d
  LEFT JOIN dba_free_space f ON d.tablespace_name = f.tablespace_name
  WHERE d.tablespace_name = v_undo_ts
  GROUP BY d.tablespace_name;

  IF v_ts_status != 'ONLINE' THEN
    DBMS_OUTPUT.PUT_LINE(
      'CRITICAL: Undo tablespace ' || v_undo_ts ||
      ' is ' || v_ts_status
    );
  ELSIF v_used_pct > 80 THEN
    DBMS_OUTPUT.PUT_LINE(
      'WARNING: Undo tablespace ' || v_undo_ts ||
      ' is ' || v_used_pct || '% full'
    );
  END IF;
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'MONITOR_UNDO_TS_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'monitor_undo_tablespace',
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=30',
    enabled         => TRUE,
    comments        => 'Monitor undo tablespace status and space usage'
  );
END;
/

2. Validate Undo Configuration After Restores and Clones

-- Post-restore checklist: run these queries after any database restore
-- 1. Confirm AUM is active
SELECT name, value FROM v$parameter WHERE name IN ('undo_management','undo_tablespace');

-- 2. Confirm the undo tablespace is ONLINE
SELECT tablespace_name, status FROM dba_tablespaces WHERE contents = 'UNDO';

-- 3. Confirm there is free space in the undo tablespace
SELECT
  d.tablespace_name,
  ROUND(SUM(d.bytes)/1024/1024/1024, 2)         AS total_gb,
  ROUND(NVL(SUM(f.bytes),0)/1024/1024/1024, 2)  AS free_gb
FROM dba_data_files d
LEFT JOIN dba_free_space f ON d.tablespace_name = f.tablespace_name
WHERE d.tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces WHERE contents='UNDO')
GROUP BY d.tablespace_name;

-- 4. Confirm autoextend is on
SELECT file_name, autoextensible, ROUND(maxbytes/1024/1024/1024,2) AS max_gb
FROM dba_data_files
WHERE tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces WHERE contents='UNDO');

3. Configuration Best Practices

• Always use Automatic Undo Management (UNDO_MANAGEMENT=AUTO) for all Oracle 9i and later databases — manual rollback segment management is obsolete and error-prone
• Size the undo tablespace generously: use the formula UNDO_SIZE = UNDO_RETENTION * UPS * DB_BLOCK_SIZE * 1.1, where UPS is undo blocks generated per second (from v$undostat)
• Enable AUTOEXTEND ON on undo tablespace datafiles with a reasonable MAXSIZE to handle burst workloads
• Set UNDO_RETENTION to cover the longest expected long-running query or report
• Always include undo tablespace datafiles in the RMAN backup — without them, the undo tablespace cannot be restored

Diagnostic Scripts

These Oracle Day by Day scripts can help diagnose undo-related issues:

• vrolluse.sql — Rollback and undo segment usage analysis

Related Errors

• ORA-01555 - Snapshot too old
• ORA-01650 - Unable to extend rollback segment
• ORA-30036 - Unable to extend segment in undo tablespace
• ORA-01653 - Unable to extend table

Emergency Response

Immediate Assessment

-- What undo management mode is the database in?
SELECT name, value FROM v$parameter WHERE name IN ('undo_management', 'undo_tablespace');

-- Is there an undo tablespace and is it online?
SELECT tablespace_name, status FROM dba_tablespaces WHERE contents = 'UNDO';

-- Are there any online user rollback segments?
SELECT segment_name, status FROM dba_rollback_segs
WHERE segment_name != 'SYSTEM' ORDER BY status;

Immediate Actions

1. Identify the undo management mode — AUM (UNDO_MANAGEMENT=AUTO) and manual management have different fixes
2. For AUM databases: verify the UNDO_TABLESPACE parameter points to an existing, online undo tablespace; if not, create one immediately and run ALTER SYSTEM SET undo_tablespace = ...
3. For manual undo databases: bring rollback segments online immediately with ALTER ROLLBACK SEGMENT ... ONLINE, then plan migration to AUM
4. Do not attempt to run DML against non-SYSTEM tablespaces until undo is available — transactions will fail immediately

Post-Resolution Verification

-- Confirm undo is now fully functional
SHOW PARAMETER undo;

-- Confirm tablespace is online
SELECT tablespace_name, status, retention FROM dba_tablespaces WHERE contents = 'UNDO';

-- Confirm transactions can now use undo (run a test DML in a non-system schema)
-- SELECT * FROM v$undostat ORDER BY begin_time DESC FETCH FIRST 3 ROWS ONLY;

-- Review recent undo activity to confirm normal operation
SELECT
  TO_CHAR(begin_time, 'DD-MON-YY HH24:MI') AS period,
  undoblks,
  maxquerylen,
  ssolderrcnt,
  unxpstealcnt
FROM v$undostat
ORDER BY begin_time DESC
FETCH FIRST 10 ROWS ONLY;