UNDO_TABLESPACE - Configure Oracle Undo for Transactions

UNDO_TABLESPACE

Overview

UNDO_TABLESPACE specifies which undo tablespace the database instance should use for storing undo data. Undo data is written by Oracle to record the before-image of changed data, enabling read consistency, transaction rollback, and Flashback operations. When Automatic Undo Management (AUM) is active — the default in modern Oracle releases — this parameter controls which dedicated UNDO tablespace handles those writes.

In a single-instance database there is typically one undo tablespace in use at a time. In a RAC environment each instance requires its own dedicated undo tablespace. Specifying the wrong or a non-existent tablespace at startup prevents the instance from opening.

Parameter Type: Dynamic (ALTER SYSTEM) Default Value: First available UNDO tablespace found in the data dictionary (none hardcoded) Valid Range: Any tablespace created with CREATE UNDO TABLESPACE Available Since: Oracle 9i (Automatic Undo Management) Modifiable: Yes — ALTER SYSTEM (takes effect immediately for new transactions; in-flight transactions continue on the old tablespace until they commit or roll back) PDB Modifiable: Yes — each PDB can designate its own local undo tablespace (Oracle 12.2+ with local undo mode enabled)

Configuration

Viewing Current Value

Check both the in-memory value and the value stored in the server parameter file (SPFILE):

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

-- Value stored in SPFILE (survives restart)
SELECT name, value
FROM   v$spparameter
WHERE  name = 'undo_tablespace';

-- All UNDO tablespaces in the database and their status
SELECT tablespace_name,
       status,
       contents,
       retention
FROM   dba_tablespaces
WHERE  contents = 'UNDO'
ORDER BY tablespace_name;

Setting the Parameter

-- Switch the instance to a different undo tablespace (dynamic, no restart needed)
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2';

-- Persist the change to the SPFILE for future restarts
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2' SCOPE = BOTH;

-- Set only in SPFILE (takes effect on next restart)
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2' SCOPE = SPFILE;

After switching, verify the change is active:

SELECT name, value FROM v$parameter WHERE name = 'undo_tablespace';

Creating a New Undo Tablespace

-- Create a new undo tablespace with autoextend enabled
CREATE UNDO TABLESPACE undotbs2
  DATAFILE '/u01/oradata/PROD/undotbs02.dbf'
  SIZE 4G
  AUTOEXTEND ON NEXT 512M
  MAXSIZE 20G;

-- Switch to the new tablespace
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2' SCOPE = BOTH;

Tuning Guidance

Recommended Sizes

Environment	Initial Size	Notes
Development / Test	512 MB – 2 GB	Autoextend sufficient
Small OLTP (< 50 sessions)	2 – 8 GB	Monitor weekly
Medium OLTP (50–500 sessions)	8 – 20 GB	Size for peak load
Large OLTP / mixed workload	20 – 50 GB	Use V$UNDOSTAT sizing formula
Data Warehouse / batch	10 – 30 GB	Long-running queries drive size

How to Size

Use V$UNDOSTAT to derive the correct undo tablespace size based on actual workload history. Each row covers a 10-minute interval.

-- Review undo generation rate over the past 24 hours
SELECT to_char(begin_time, 'DD-MON-YYYY HH24:MI') AS interval_start,
       undoblks                                   AS undo_blocks_used,
       txncount                                   AS transactions,
       maxquerylen                                AS longest_query_secs,
       maxconcurrency                             AS peak_concurrent_txns,
       ssolderrcnt                                AS ora_01555_errors,
       nospaceerrcnt                              AS ora_30036_errors
FROM   v$undostat
WHERE  begin_time >= sysdate - 1
ORDER BY begin_time DESC;

-- Oracle-recommended undo tablespace size formula
-- UndoSize = UndoRetention * UndoBlocksPerSecond * BlockSize
SELECT d.value                              AS db_block_size_bytes,
       u.undoblks / (u.maxquerylen + 1)    AS undo_blocks_per_sec,
       p.value                             AS undo_retention_secs,
       ROUND(
         (p.value * (u.undoblks / NULLIF(u.maxquerylen, 0)) * d.value) / (1024*1024*1024),
         2
       )                                   AS recommended_gb
FROM  (SELECT max(undoblks)    AS undoblks,
              max(maxquerylen) AS maxquerylen
         FROM v$undostat
        WHERE begin_time >= sysdate - 1)   u,
      (SELECT value FROM v$parameter WHERE name = 'db_block_size')  d,
      (SELECT value FROM v$parameter WHERE name = 'undo_retention') p;

Monitoring Undo Tablespace Usage

-- Current space usage within the active undo tablespace
SELECT tablespace_name,
       status,
       SUM(bytes) / (1024*1024*1024)  AS total_gb,
       COUNT(*)                        AS extents
FROM   dba_undo_extents
GROUP BY tablespace_name, status
ORDER BY tablespace_name, status;

-- Proportion of undo space in each state
SELECT status,
       COUNT(*)                                AS extents,
       ROUND(SUM(bytes)/1024/1024, 1)          AS mb,
       ROUND(SUM(bytes)/SUM(SUM(bytes)) OVER () * 100, 1) AS pct
FROM   dba_undo_extents
GROUP BY status;

-- Free vs used space in the undo tablespace datafiles
SELECT f.tablespace_name,
       ROUND(SUM(f.bytes)/1024/1024/1024, 2)   AS free_gb,
       ROUND(d.total_gb, 2)                    AS total_gb,
       ROUND((1 - SUM(f.bytes) / (d.total_gb * 1024*1024*1024)) * 100, 1) AS pct_used
FROM   dba_free_space f
JOIN  (SELECT tablespace_name, SUM(bytes)/1024/1024/1024 AS total_gb
         FROM dba_data_files
        GROUP BY tablespace_name) d
  ON  f.tablespace_name = d.tablespace_name
WHERE  f.tablespace_name IN (
         SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO')
GROUP BY f.tablespace_name, d.total_gb;

Common Issues

Issue 1: Instance fails to start — undo tablespace not found

Symptom: ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type during startup.

Cause: The SPFILE references a tablespace name that does not exist in the data dictionary, or the tablespace was dropped while a different undo tablespace was active.

Resolution:

-- Start in MOUNT state and correct the SPFILE
STARTUP MOUNT;
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2' SCOPE = SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

Issue 2: ORA-30036 — Unable to extend undo segment

Symptom: DML statements fail with ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'.

Cause: The undo tablespace has no free space and cannot autoextend (either autoextend is off or MAXSIZE has been reached).

Resolution:

-- Check if autoextend is enabled on the datafile(s)
SELECT file_name, bytes/1024/1024 AS mb, autoextensible, maxbytes/1024/1024 AS max_mb
FROM   dba_data_files
WHERE  tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace');

-- Add a new datafile if autoextend cannot help
ALTER TABLESPACE undotbs1
  ADD DATAFILE '/u01/oradata/PROD/undotbs01b.dbf'
  SIZE 4G AUTOEXTEND ON NEXT 512M MAXSIZE 20G;

Issue 3: Switching undo tablespace leaves old tablespace in PENDING OFFLINE state

Symptom: After running ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2', the old tablespace shows STATUS = PENDING OFFLINE in DBA_TABLESPACES.

Cause: Active or unexpired undo extents in the old tablespace are still being retained. The old tablespace cannot go offline until those extents expire.

Resolution: This is normal behaviour. Wait until all extents transition to EXPIRED status, then the tablespace can be dropped or taken offline. Monitor with DBA_UNDO_EXTENTS WHERE status IN ('ACTIVE','UNEXPIRED').

UNDO_RETENTION — Minimum seconds to retain committed undo; directly affects how long the old tablespace stays in PENDING OFFLINE state.
UNDO_MANAGEMENT — Must be AUTO (default) for UNDO_TABLESPACE to apply.
DB_BLOCK_SIZE — Affects the undo tablespace sizing formula (block size in bytes).

ORA-30036 — Unable to extend undo segment; undo tablespace full.
ORA-01555 — Snapshot too old; undo expired before long query completed.
ORA-01552 — Cannot use system rollback segment for non-system tablespace; occurs when UNDO_MANAGEMENT=MANUAL is accidentally set.
ORA-30012 — Undo tablespace does not exist; SPFILE points to missing tablespace.

Version Notes

Version	Notes
Oracle 9i	Automatic Undo Management (AUM) introduced; `UNDO_TABLESPACE` replaces manual rollback segment management.
Oracle 10g	Default `UNDO_MANAGEMENT = AUTO`; parameter becomes effectively mandatory for normal operation.
Oracle 12.2	Local undo mode for PDBs introduced; each PDB can have its own undo tablespace and `UNDO_TABLESPACE` can be set at PDB level.
Oracle 19c	Local undo mode is the default for newly created CDBs; `UNDO_TABLESPACE` must be set for each PDB individually in local undo mode.
Oracle 21c+	No functional changes; local undo remains the recommended model.

UNDO_TABLESPACE - Configure Oracle Undo for Transactions

UNDO_TABLESPACE

Overview

Configuration

Viewing Current Value

Setting the Parameter

Creating a New Undo Tablespace

Tuning Guidance

Recommended Sizes

How to Size

Monitoring Undo Tablespace Usage

Common Issues

Issue 1: Instance fails to start — undo tablespace not found

Issue 2: ORA-30036 — Unable to extend undo segment

Issue 3: Switching undo tablespace leaves old tablespace in PENDING OFFLINE state

Related Parameters

Related Errors

Version Notes