Oracle事务提交后Undo表空间变化及超undo_retention的记录存储问题

Let’s tackle your questions one by one—undo management and flashback behavior can feel a bit opaque at first glance, so I’ll break it down clearly:

1. After committing a transaction: Where do old data values go, and what changes happen to the undo tablespace? #

When you commit a transaction, Oracle doesn’t immediately delete or erase the old data values stored in undo segments. Instead:

• The undo blocks holding those old values are marked as available for reuse, but the data stays on disk until Oracle needs the space for new undo operations.
• For the undo tablespace specifically:
  • Entries in the undo segment’s transaction table get cleaned up, marking the relevant undo blocks as UNEXPIRED (if time since commit is still under your undo_retention setting) or EXPIRED (if it’s exceeded the threshold).
  • Logically, free space in the undo tablespace increases (this doesn’t shrink the tablespace physically unless you enable auto-shrink or run a manual shrink command), making that space available for future transactions.
  • If your undo tablespace is set to auto-extend, committing won’t trigger an automatic shrink—it’ll only stop growing if there’s enough free space to handle subsequent operations.

You can confirm this by querying the DBA_UNDO_EXTENTS view, where you’ll see extents switch from ACTIVE to UNEXPIRED/EXPIRED after a commit.

2. Why can I flashback a table even after exceeding undo_retention, and where are those records stored? #

This typically happens in two scenarios:

Scenario 1: Undo blocks haven’t been overwritten yet #

The undo_retention parameter is a target retention time, not a hard rule. If your undo tablespace has plenty of free space, Oracle won’t overwrite UNEXPIRED undo blocks even if they’ve passed the retention threshold. So if the old data’s undo blocks haven’t been reused for new transactions, you can still flash back to that point. Note though—this is unreliable; you can’t count on it if the undo tablespace is under space pressure.

Scenario 2: Flashback Archive (Total Recall) is enabled #

If you’ve configured Flashback Archive for the table (via ALTER TABLE your_table FLASHBACK ARCHIVE flashback_archive_name;), Oracle automatically archives all historical changes to a dedicated flashback archive tablespace. This storage is completely separate from the undo tablespace, and it follows its own retention policy (which you define when creating the flashback archive).

Flashback Archive data is persistent—even if undo blocks are overwritten, the archived historical records remain until their defined retention period expires. This is the reliable way to retain historical data for long-term flashback operations.

内容的提问来源于stack exchange，提问作者Saman