Master in AWS | New Batch Starting From 14th Oct 2025 at 7 PM IST | Register for Free Demo

Undo Tablespace Management in Oracle Database

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarPradip
  • 27 Oct, 2025
  • 0 Comments
  • 3 Mins Read

Undo Tablespace Management in Oracle Database

In Oracle Database, the Undo Tablespace plays a crucial role in maintaining database consistency and supporting key features such as transaction rollback, read consistency, and flashback operations.
When running Oracle Database on Oracle Linux, managing the Undo tablespace efficiently ensures better performance and prevents issues like ORA-30036 (“unable to extend segment by…”).

What is an Undo Tablespace?

An Undo tablespace stores undo records generated by DML operations (INSERT, UPDATE, DELETE).
These records are used to:

  • Roll back uncommitted transactions

  • Provide read consistency for queries

  • Support flashback features like FLASHBACK QUERY and FLASHBACK TABLE

  • Recover transactions during instance recovery

Types of Undo Management in Oracle

Oracle offers two undo management modes:

  1. Manual Undo Management (Deprecated)

    • Uses rollback segments manually created and managed by DBAs.

    • Obsolete from Oracle 9i onwards.

  2. Automatic Undo Management (AUM)  (Recommended)

    • Managed automatically by Oracle.

    • You only need to create and assign undo tablespaces.

    • Controlled by the initialization parameter:

      UNDO_MANAGEMENT=AUTO

How Undo Tablespace Works

When a transaction modifies data:

  • Old values are written to the undo tablespace.

  • If the transaction is rolled back, Oracle restores these old values.

  • Queries that started before the change can still see the consistent old data.

Oracle automatically reuses undo space when it’s no longer needed, based on undo retention.

Key Undo Parameters

Parameter Description
UNDO_MANAGEMENT Should be set to AUTO for automatic management
UNDO_TABLESPACE Defines which undo tablespace is used
UNDO_RETENTION Time (in seconds) Oracle tries to retain undo data
RETENTION GUARANTEE Ensures undo is kept for the full retention period

Creating an Undo Tablespace

Run the following SQL as SYSDBA:

CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/app/oracle/oradata/ORCL/undotbs02.dbf'
SIZE 200M
AUTOEXTEND ON
RETENTION NOGUARANTEE;

Switching to a New Undo Tablespace

If you want to switch the active undo tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2;

You can then drop the old one (after verifying it’s not in use):

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Viewing Undo Tablespace Information

To check which undo tablespace is active:

SHOW PARAMETER undo_tablespace;

Check undo usage:

SELECT a.tablespace_name, a.file_id, a.bytes/1024/1024 AS size_mb,
b.tuned_undoretention
FROM dba_data_files a, v$undostat b
WHERE a.tablespace_name LIKE 'UNDO%';

Setting Undo Retention

Undo retention determines how long undo data is preserved after a transaction commits.
Set it using:

ALTER SYSTEM SET UNDO_RETENTION = 900;

If you want Oracle to strictly keep undo data for 900 seconds:

ALTER TABLESPACE undotbs2 RETENTION GUARANTEE;

Common Issues

Issue Cause Solution
ORA-30036: unable to extend segment in undo tablespace Undo tablespace too small Increase undo tablespace size
Undo tablespace growing too fast Long-running queries or poor retention settings Tune UNDO_RETENTION and monitor with V$UNDOSTAT
Slow performance High undo I/O Move undo datafile to faster storage (ASM/SSD)

Best Practices for Undo Management

Use Automatic Undo Management (UNDO_MANAGEMENT=AUTO)
Create separate undo tablespaces for different PDBs in Multitenant environments
Enable AUTOEXTEND for undo datafiles
Monitor undo space with V$UNDOSTAT and DBA_UNDO_EXTENTS
Periodically resize undo tablespaces based on workload trends

Undo Management in Oracle RAC

In Oracle RAC, each instance must have its own undo tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS1 SID='RAC1';
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SID='RAC2';

This ensures proper transaction isolation between nodes.

Conclusion

Efficient Undo Tablespace Management is vital for database stability, consistency, and performance.
By configuring Automatic Undo Management, setting appropriate retention, and monitoring regularly, DBAs can ensure smooth transaction recovery and consistent query results — even in complex RAC or multitenant setups.

Final Thoughts

Managing the Undo Tablespace effectively is one of the key responsibilities of every Oracle DBA. A well-tuned undo setup not only ensures smooth rollback and consistent reads but also improves overall database stability and performance. Whether you’re running a single-instance database or a complex RAC environment on Oracle Linux, always monitor undo usage trends, size the tablespace appropriately, and fine-tune retention settings based on workload patterns.

Continuous learning and proactive management will help you maintain an efficient and reliable Oracle environment — ready to handle both routine operations and unexpected recovery scenarios with confidence.

📚 Learn More with Learnomate Technologies
🔗 Website: www.learnomate.org
📺 YouTube: youtube.com/@learnomate
💼 LinkedIn: https://www.linkedin.com/in/ankushthavali/

Understanding Database Checkpoints