Managing Undo Space in Oracle AI Database 26ai

Earlier versions of this lesson focused on creating rollback segments by hand. That instruction has aged out completely — not just stylistically, but functionally. This lesson explains why, walks through the legacy syntax as a piece of Oracle history worth understanding, and then covers the tuning lever that actually applies to your COIN database today: UNDO_RETENTION.

Starting with Oracle 9i, Automatic Undo Management (AUM)^[1] has been the default mode for handling undo data, and it remains the mode Oracle AI Database 26ai uses. Rather than administrators manually creating and sizing rollback segments, Oracle allocates and manages undo records automatically within a dedicated UNDO tablespace — the one you created back in Lesson 11 alongside SYSTEM, SYSAUX, and TEMP.

In older Oracle versions, before AUM existed, rollback segments were sometimes placed in the SYSTEM tablespace when no better option was configured. That was always considered poor practice — it added extra I/O load to an already busy tablespace — and it isn't something you'll need to think about at all with AUM in place. Oracle allocates undo in the designated UNDO tablespace automatically, full stop.

There's a stronger reason to leave rollback segments behind than "Oracle recommends against it." The UNDO_MANAGEMENT initialization parameter controls which mode a database uses: AUTO for automatic undo management, or MANUAL for the old rollback-segment-based approach. In Oracle AI Database 26ai, that choice isn't actually available to you. Oracle's own documentation states it plainly:

"In a CDB, the UNDO_MANAGEMENT initialization parameter must be set to AUTO, and an undo tablespace is required to be created to manage the undo data."

Since Oracle Database 21c, every Oracle database is a CDB — this course established that back in Lesson 9, and it applied directly to the CREATE DATABASE script in Lesson 11. Put the two facts together: a CDB requires UNDO_MANAGEMENT = AUTO, and COIN is a CDB, so manual undo management — and the rollback segments that mode depends on — simply isn't a legal configuration for your database. This isn't a "still works, but discouraged" situation. The setting that would enable it doesn't exist as an option.

It's still worth understanding what rollback segments looked like, both to recognize the syntax if you encounter it in old scripts or documentation, and to appreciate what AUM actually saved administrators from managing by hand. Treat everything in this section as history, not instruction — none of it is runnable against COIN.

CREATE [PUBLIC] ROLLBACK SEGMENT segment_name
[TABLESPACE tablespace_name]
[STORAGE (INITIAL integer [M|K],
    NEXT integer [M|K],
    MINEXTENTS integer,
    MAXEXTENTS integer,
    OPTIMAL integer)];

A worked example, purely for illustration:

CREATE PUBLIC ROLLBACK SEGMENT rbs_one
  TABLESPACE rbs_ts
  STORAGE (
    INITIAL    10M
    NEXT        1M
    MINEXTENTS  2
    MAXEXTENTS 100
    OPTIMAL     20M
  );

The TABLESPACE and STORAGE clauses could appear in any order, and every STORAGE parameter was optional — you specified only the ones where you didn't want the default. You were always expected to specify a tablespace explicitly; if you didn't, the SYSTEM tablespace was used by default, which — as noted above — was considered bad practice due to the added I/O contention.

With rollback segments off the table, the meaningful undo-related setting for an administrator today is UNDO_RETENTION. It controls, in seconds, the minimum length of time Oracle retains undo data before it's eligible to be overwritten — which matters for read consistency on long-running queries and for Flashback features that look backward in time.

The default value is 900 seconds (15 minutes), and Oracle's own guidance is to leave it there in most cases:

"Oracle generally recommends that you leave UNDO_RETENTION set to its default value."

There are two situations where increasing it makes sense: if you're relying on Oracle Flashback Query and need undo retained longer than your longest-running query, or in an Oracle Active Data Guard setup, where the primary may need to retain undo longer to serve queries running against a standby. Outside of those cases, the default is doing its job.

One more wrinkle worth knowing, and it's the same "which container does this apply to" question this course keeps running into: UNDO_RETENTION can be set independently in the CDB root and in each individual PDB — they don't have to match. There's a catch, though: you can only modify this parameter inside a PDB that's running in local undo mode. If a PDB is in shared undo mode, its UNDO_RETENTION isn't independently settable. And since Oracle Database 19c (specifically, release 19.9), a PDB no longer inherits its value from the CDB root automatically — changing it at the root doesn't silently change it everywhere underneath.

If you followed Lesson 11's CREATE DATABASE script for COIN, local undo mode is already enabled (LOCAL UNDO ON), so this level of per-PDB control is available to you as soon as you create your first PDB.

To see what retention value Oracle is actually using — which can differ from the configured minimum, since Oracle auto-tunes retention upward when the undo tablespace has room to support it — query the tuned value directly:

SELECT TUNED_UNDORETENTION
FROM V$UNDOSTAT
ORDER BY END_TIME DESC
FETCH FIRST 1 ROW ONLY;

If you ever see queries fail with a "snapshot too old" error, it's usually a sign that active transactions needed undo space the tablespace didn't have, forcing Oracle to reuse unexpired undo ahead of the retention window you configured — worth checking V$UNDOSTAT and undo tablespace sizing together when that happens, rather than just raising UNDO_RETENTION in isolation.

The exercise below predates Automatic Undo Management becoming mandatory in a CDB, and its original instructions — writing CREATE ROLLBACK SEGMENT commands against COIN — won't execute against your database, for exactly the reason covered above. Use it instead as a chance to work with what actually applies today: connect to COIN, check the current UNDO_RETENTION value and tuned retention from V$UNDOSTAT, and try adjusting UNDO_RETENTION at the CDB root versus inside a PDB in local undo mode, to see the independent-scope behavior described above firsthand.

Click the Exercise link below to practice what you have learned. In this exercise, you will write some CREATE ROLLBACK SEGMENT commands for your COIN database.
Create Rollback Segments - Exercise

[1] Automatic Undo Management: Automatic Undo Management simplifies undo space management by eliminating the need for manual rollback segment management. It uses undo tablespaces to store undo information, which is essential for transaction rollback, read consistency, and database recovery. In a CDB — the only supported architecture since Oracle Database 21c — AUM isn't just the default; it's the only available mode.