How to Switch Undo Tablespace in Oracle Online

How to Switch Undo Tablespace in Oracle Online

Switching the active undo tablespace is a fully online operation in Oracle — no database restart is required. The typical reasons to switch are: the current undo tablespace is too small, it is on slow storage, or it is fragmented beyond practical recovery. This guide walks through the complete process safely.

Why Switch Undo Tablespace?

• Too small: Long-running transactions exhaust undo space, causing ORA-01555 (snapshot too old) or ORA-30036 (unable to extend undo segment)
• Wrong location: The tablespace is on a slower disk or needs to move to different storage
• Fragmentation: Undo segments are fragmented and have grown excessively large
• Pre-migration: Preparing for a storage migration or tablespace reorganization

Step 1: Assess the Current Undo Situation

-- Check which undo tablespace is currently active
SHOW PARAMETER undo_tablespace;

-- Check current undo tablespace size and usage
SELECT
  t.tablespace_name,
  ROUND(SUM(d.bytes)/1024/1024/1024, 2) AS total_gb,
  ROUND(NVL(SUM(f.free),0)/1024/1024/1024, 2) AS free_gb,
  ROUND((1 - NVL(SUM(f.free),0)/SUM(d.bytes)) * 100, 1) AS used_pct
FROM dba_tablespaces t
JOIN dba_data_files d ON t.tablespace_name = d.tablespace_name
LEFT JOIN (
  SELECT tablespace_name, SUM(bytes) AS free
  FROM dba_free_space GROUP BY tablespace_name
) f ON t.tablespace_name = f.tablespace_name
WHERE t.contents = 'UNDO'
GROUP BY t.tablespace_name
ORDER BY t.tablespace_name;

-- Check undo segment details and status
SELECT usn, name, status, xacts,
       ROUND(rssize/1024/1024, 0) AS size_mb,
       ROUND(hwmsize/1024/1024, 0) AS hwm_mb
FROM v$rollstat r
JOIN v$rollname rn ON r.usn = rn.usn
ORDER BY usn;

-- Check for active transactions in current undo tablespace
SELECT COUNT(*) AS active_transactions
FROM v$transaction;

-- Check undo extents by status
SELECT tablespace_name, status,
       COUNT(*) AS extents,
       ROUND(SUM(bytes)/1024/1024, 0) AS total_mb
FROM dba_undo_extents
GROUP BY tablespace_name, status
ORDER BY tablespace_name, status;

Step 2: Create the New Undo Tablespace

-- Create a properly sized undo tablespace
-- Size recommendation: enough for peak transaction load * undo_retention time
-- Formula: undo_space = transactions/sec * average_block_size * undo_retention_seconds
CREATE UNDO TABLESPACE undo_new
  DATAFILE '+DATA/ORCL/DATAFILE/undo_new01.dbf' SIZE 5G
  AUTOEXTEND ON NEXT 1G MAXSIZE 30G;

-- For a filesystem installation:
CREATE UNDO TABLESPACE undo_new
  DATAFILE '/u01/oradata/ORCL/undo_new01.dbf' SIZE 5G
  AUTOEXTEND ON NEXT 1G MAXSIZE 30G;

-- Verify the new tablespace is created and online
SELECT tablespace_name, status, contents, extent_management
FROM dba_tablespaces
WHERE tablespace_name = 'UNDO_NEW';

Step 3: Switch the Active Undo Tablespace

This is a dynamic parameter — the switch takes effect immediately without a restart.

-- Switch to the new undo tablespace (takes effect immediately)
ALTER SYSTEM SET undo_tablespace = 'UNDO_NEW' SCOPE=BOTH;
-- SCOPE=BOTH updates both the running instance AND the SPFILE

-- Verify the switch was successful
SHOW PARAMETER undo_tablespace;
-- Should show: undo_tablespace = UNDO_NEW

-- Confirm in v$parameter
SELECT name, value, description
FROM v$parameter
WHERE name = 'undo_tablespace';

Step 4: Wait for the Old Undo Tablespace to Clear

After switching, Oracle stops assigning new transactions to the old undo tablespace, but existing transactions and pending undo retention continue to use it. You cannot drop it until it is fully empty.

-- Monitor the old undo tablespace until all extents are EXPIRED
-- This may take as long as the undo_retention parameter (default 900 seconds)
SELECT tablespace_name, status,
       COUNT(*) AS extents,
       ROUND(SUM(bytes)/1024/1024, 0) AS total_mb
FROM dba_undo_extents
WHERE tablespace_name = 'UNDOTBS1'  -- Replace with old tablespace name
GROUP BY tablespace_name, status
ORDER BY status;

-- Wait until only EXPIRED extents remain (no ACTIVE or UNEXPIRED)
-- ACTIVE = transaction in progress (cannot drop)
-- UNEXPIRED = undo within retention window (safe to drop, but wastes flashback)
-- EXPIRED = beyond retention window (safe to drop)

-- Check if any segments are still ACTIVE in the old tablespace
SELECT s.segment_name, s.status, s.tablespace_name
FROM dba_segments s
WHERE s.tablespace_name = 'UNDOTBS1'
  AND s.segment_type = 'TYPE2 UNDO';

Step 5: Drop the Old Undo Tablespace

-- Drop the old undo tablespace once it shows only EXPIRED extents or is empty
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
-- INCLUDING CONTENTS removes all segments
-- AND DATAFILES removes the physical file(s) from disk (on OMF/ASM)
-- On filesystem without OMF, the physical file must be deleted manually via OS

-- Verify it is gone
SELECT tablespace_name FROM dba_tablespaces
WHERE tablespace_name = 'UNDOTBS1';
-- No rows = success

If the tablespace cannot be dropped because it still has ACTIVE extents:

-- Check which transactions are still active in the old tablespace
SELECT t.addr, t.xidusn, t.xidslot, t.xidsqn,
       ROUND(t.used_ublk * 8192 / 1024, 0) AS undo_kb,
       s.sid, s.serial#, s.username, s.status, s.last_call_et
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
JOIN v$rollname rn ON t.xidusn = rn.usn
WHERE rn.name LIKE '_SYSSMU%'
ORDER BY t.used_ublk DESC;

Wait for those transactions to commit or roll back, then retry the DROP.

Advanced Examples

Size the New Undo Tablespace Correctly

-- Calculate recommended undo size based on current workload
SELECT
  ROUND(max_undo_gen_mb * undo_retention / 60, 0) AS recommended_mb,
  max_undo_gen_mb,
  undo_retention,
  current_undo_gb
FROM (
  SELECT
    ROUND(MAX(undoblks) * 8192 / 1024 / 1024, 0) AS max_undo_gen_mb,
    (SELECT TO_NUMBER(value) FROM v$parameter WHERE name = 'undo_retention') AS undo_retention,
    (SELECT ROUND(SUM(bytes)/1024/1024/1024, 1)
     FROM dba_data_files
     WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace')
    ) AS current_undo_gb
  FROM v$undostat
  WHERE begin_time > SYSDATE - 7
);

Switch Undo Tablespace for a Single PDB (CDB)

-- In a CDB, connect to the specific PDB
ALTER SESSION SET CONTAINER = pdb_prod;

-- Create and switch undo within the PDB
CREATE UNDO TABLESPACE pdb_undo_new
  DATAFILE SIZE 2G AUTOEXTEND ON NEXT 512M MAXSIZE 20G;

ALTER SYSTEM SET undo_tablespace = 'PDB_UNDO_NEW' SCOPE=BOTH;

Autoextend Tuning for Undo

-- Prevent undo from growing unbounded while still allowing flexibility
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/undo_new01.dbf'
  AUTOEXTEND ON NEXT 1G MAXSIZE 50G;

-- Add a second datafile for I/O distribution
ALTER TABLESPACE undo_new
  ADD DATAFILE '/u02/oradata/ORCL/undo_new02.dbf' SIZE 5G
  AUTOEXTEND ON NEXT 1G MAXSIZE 30G;

Common Mistakes and Pitfalls

Dropping the old undo tablespace while ACTIVE extents remain — Oracle will raise ORA-30013 (undo tablespace is currently in use). Check dba_undo_extents and wait for ACTIVE extents to clear.

Setting SCOPE=MEMORY only — Using ALTER SYSTEM SET undo_tablespace=... SCOPE=MEMORY changes the running instance but not the SPFILE. After a restart, the old tablespace name is used again. Always use SCOPE=BOTH.

Not sizing the new tablespace for peak workload — An undersized undo tablespace causes ORA-30036 (extend undo segment) during high-transaction periods. Use the sizing query in the Advanced section.

Forgetting undo_retention — After switching, verify undo_retention is appropriate for the new tablespace size. Too high a retention value wastes space; too low causes ORA-01555 for long queries.

Using filesystem paths without removing the file manually — DROP TABLESPACE ... INCLUDING CONTENTS AND DATAFILES only deletes the file automatically for OMF-managed files on ASM or when db_create_file_dest is set. On raw filesystem, you must rm the file manually after the drop.

Verification Queries

-- Confirm the new undo tablespace is active and the old one is gone
SELECT tablespace_name, status, contents
FROM dba_tablespaces
WHERE contents = 'UNDO'
ORDER BY tablespace_name;

-- Confirm the SPFILE parameter was updated
SELECT name, value FROM v$parameter WHERE name = 'undo_tablespace';

-- Confirm all undo activity is now in the new tablespace
SELECT name, status, tablespace_name
FROM v$rollstat rs
JOIN v$rollname rn ON rs.usn = rn.usn
JOIN dba_rollback_segs drs ON rn.name = drs.segment_name
ORDER BY rs.usn;

-- Confirm no old datafiles remain
SELECT tablespace_name, file_name
FROM dba_data_files
WHERE tablespace_name LIKE 'UNDO%'
ORDER BY tablespace_name;

-- Check undo advisory statistics after the switch
SELECT begin_time, end_time, undoblks, txncount,
       maxquerylen, ssolderrcnt, nospaceerrcnt
FROM v$undostat
WHERE begin_time > SYSDATE - 1
ORDER BY begin_time DESC
FETCH FIRST 24 ROWS ONLY;

Related Topics

• How to Create a Tablespace - Create undo tablespace from scratch
• How to Monitor Tablespace Usage - Track undo space growth
• Oracle Errors: ORA-01555 - Snapshot too old (undo exhaustion)
• Oracle Errors: ORA-30036 - Unable to extend undo segment