Undo Segments, Retention, Read Consistency, Flashback Dependencies, and Transaction Recovery

Learning outcomes

A ServiceHub report must see one consistent snapshot while users update rows. A team shrinks undo and sets UNDO_RETENTION=7200, assuming two hours is guaranteed; the report later receives ORA-01555. Undo is not only for manual rollback. It stores older information used for transaction rollback, consistent-read reconstruction, transaction recovery, and many Flashback operations.

1. Undo reverses and reconstructs

Before changing a block, Oracle records information that can reverse the change. Undo lets a transaction roll back, lets a query reconstruct the block image valid at its statement SCN, lets crash recovery remove uncommitted work after redo roll-forward, and lets Flashback Query reconstruct prior versions while the necessary undo still exists.

Redo answers “how do I reapply changes?” Undo answers “how do I reverse/reconstruct them?” Recovery needs both roles.

2. Automatic Undo Management manages segments

SELECT name,value,issys_modifiable,ispdb_modifiableFROM v$parameterWHERE name IN ('undo_management','undo_tablespace','undo_retention')ORDER BY name;SELECT property_name,property_valueFROM database_propertiesWHERE property_name='LOCAL_UNDO_ENABLED';

Automatic Undo Management (AUM) allocates/manages undo segments in the active undo tablespace. In local-undo multitenant mode, PDBs have their own undo context rather than one shared CDB undo pool.

3. UNDO_RETENTION is normally a target, not a guarantee

Oracle can overwrite unexpired undo under space pressure even when UNDO_RETENTION is larger than the query age. A retention-guaranteed undo tablespace prevents reuse of unexpired undo, but then foreground DML can fail when there is no reusable space.

SELECT tablespace_name,retention,status,contentsFROM dba_tablespacesWHERE contents='UNDO'ORDER BY tablespace_name;

4. Measure achieved retention and failure evidence

SELECT * FROM (  SELECT begin_time,end_time,undoblks,txncount,maxquerylen,maxqueryid,         tuned_undoretention,ssolderrcnt,nospaceerrcnt  FROM v$undostat  ORDER BY end_time DESC) FETCH FIRST 12 ROWS ONLY;

TUNED_UNDORETENTION is Oracle's tuned retention estimate. MAXQUERYLEN shows the longest observed query duration in the interval. SSOLDERRCNT counts snapshot-too-old failures, and NOSPACEERRCNT indicates failure to obtain undo space for active work. Historical DBA_HIST_UNDOSTAT is not used in the mandatory path because AWR usage is management-pack sensitive on offerings where Diagnostics Pack is not included/licensed.

5. Flashback Query makes the older-version mechanism visible

SHOW CON_NAME-- FREEPDB1BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_undo_case PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_undo_case(  case_id NUMBER PRIMARY KEY,  status_code VARCHAR2(12) NOT NULL);INSERT INTO servicehub_undo_case VALUES(1,'OPEN');COMMIT;VARIABLE before_scn NUMBERBEGIN  :before_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;END;/UPDATE servicehub_undo_case SET status_code='CLOSED' WHERE case_id=1;COMMIT;SELECT status_code AS current_value FROM servicehub_undo_case WHERE case_id=1;SELECT status_code AS saved_scn_valueFROM servicehub_undo_case AS OF SCN :before_scnWHERE case_id=1;

Expected values are CLOSED now and OPEN at the saved SCN while the old version remains reconstructable. This proves a short historical lookup, not a guaranteed retention window.

6. ORA-01555 is an undo-history availability failure

ORA-01555 (“snapshot too old”) occurs when a long-running query needs an older version whose undo has already been overwritten. The safe response is to measure long-query duration, undo generation, datafile capacity/autoextend, tuned retention, and concurrent transactions—not merely assign a huge parameter value.

SELECT begin_time,end_time,tuned_undoretention,maxquerylen,       ssolderrcnt,nospaceerrcnt,undoblksFROM v$undostatWHERE ssolderrcnt > 0 OR nospaceerrcnt > 0ORDER BY end_time DESC;SELECT tablespace_name,file_id,bytes,maxbytes,autoextensibleFROM dba_data_filesWHERE tablespace_name IN (  SELECT tablespace_name FROM dba_tablespaces WHERE contents='UNDO')ORDER BY tablespace_name,file_id;

The lab deliberately does not churn undo until ORA-01555 occurs, because doing so can disrupt unrelated work and teaches a harmful test pattern.

7. Normal rollback semantics

SAVEPOINT before_change;UPDATE servicehub_undo_case SET status_code='HOLD' WHERE case_id=1;SELECT status_code FROM servicehub_undo_case WHERE case_id=1;ROLLBACK TO before_change;SELECT status_code FROM servicehub_undo_case WHERE case_id=1;DROP TABLE servicehub_undo_case PURGE;

8. Production judgment

Size undo for concurrent update volume plus the longest required query/Flashback window. Monitor tuned retention, ORA-01555/no-space counts, long queries, datafile growth, and Flashback requirements. Use retention guarantee only when preserving unexpired undo is worth the possibility of failing new DML for space.

No pack, restart, or COMPATIBLE change is required. Lesson 3 connects redo and undo to the recovery metadata Oracle uses to know where recovery starts: control files, SCNs, checkpoint positions, datafile headers, and redo history.

Authoritative references

• Managing Undo — automatic undo and retention
• UNDO_RETENTION — retention target semantics
• Using Oracle Flashback Technology — undo-backed Flashback Query
• V$UNDOSTAT — undo workload/retention/error metrics
• Data Concurrency and Consistency — SCN-based consistent reads