Flashback Query with AS OF SCN/TIMESTAMP and Undo-Dependent Historical Reads

Learning outcomes

A ServiceHub operator accidentally changes a work order from OPEN to CLOSED and commits. The row is not “uncommitted,” so a normal ROLLBACK cannot help. If Oracle still retains the required undo, Flashback Query can read a past committed image of the row without restoring a backup or changing the current table. That ability is powerful precisely because it is temporary: the history depends on undo availability.

1. Flashback Query asks the SQL engine to reconstruct an older committed snapshot

AS OF SCN or AS OF TIMESTAMP attaches a historical snapshot to a table reference. Oracle uses Automatic Undo Management (AUM) to reconstruct blocks as they existed at that System Change Number. The query is read-only with respect to the historical image: it does not “rewind” the current table.

SELECT  SYS_CONTEXT('USERENV','CON_NAME') AS con_name,  SYS_CONTEXT('USERENV','CON_ID') AS con_idFROM dual;SELECT name,valueFROM v$parameterWHERE name IN ('undo_management','undo_retention')ORDER BY name;

Run the application lab in FREEPDB1. A non-owner querying another schema's history needs FLASHBACK plus READ/SELECT on that object, or the broader FLASHBACK ANY TABLE privilege.

2. Reproducible ServiceHub setup and exact-SCN capture

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_flash_case PURGE';EXCEPTION  WHEN OTHERS THEN    IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_flash_case (  case_id      NUMBER PRIMARY KEY,  status_code  VARCHAR2(12) NOT NULL,  amount       NUMBER(10,2) NOT NULL);INSERT INTO servicehub_flash_case VALUES (1,'OPEN',125.00);COMMIT;VARIABLE good_scn NUMBERBEGIN  :good_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;END;/PRINT good_scn

The saved SCN is the logical ordering point immediately after the known-good commit. SCN is preferable to a timestamp when an exact recovery boundary matters.

3. Commit the bad change, then compare current and historical images

UPDATE servicehub_flash_caseSET status_code='CLOSED',    amount=9999WHERE case_id=1;COMMIT;SELECT case_id,status_code,amountFROM servicehub_flash_caseWHERE case_id=1;-- 1 | CLOSED | 9999

SELECT case_id,status_code,amountFROM servicehub_flash_case AS OF SCN :good_scnWHERE case_id=1;-- Expected while undo is available:-- 1 | OPEN | 125

The historical query does not acquire a second “old table.” Oracle reconstructs the needed consistent-read version from current blocks plus undo history.

4. Timestamp flashback maps time to an approximate SCN

Oracle remembers SCN-to-time associations for a limited period. SCN_TO_TIMESTAMP returns an approximate timestamp, typically with about three-second precision. The mapping is retained for at least 120 hours and potentially longer based on tuned undo retention and Flashback Time Travel retention.

SELECT  :good_scn AS good_scn,  SCN_TO_TIMESTAMP(:good_scn) AS approximate_timeFROM dual;

SELECT case_id,status_code,amountFROM servicehub_flash_caseAS OF TIMESTAMP SCN_TO_TIMESTAMP(:good_scn)WHERE case_id=1;

Do not confuse “Oracle can still map this old SCN to a timestamp” with “Oracle still has the undo needed to reconstruct this table.” The mapping metadata can outlive the actual row-history undo.

5. Undo availability is the hard boundary

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

TUNED_UNDORETENTION is evidence of what Oracle estimates it can currently retain. SSOLDERRCNT records ORA-01555 occurrences. The configured UNDO_RETENTION is normally a target, not a guarantee unless the undo tablespace uses retention guarantee.

6. Deliberately wrong: “AS OF” means history exists forever

SELECT case_id,status_code,amountFROM servicehub_flash_case AS OF SCN :very_old_scnWHERE case_id=1;-- Typical failure after required undo was overwritten:-- ORA-01555: snapshot too old ...

ORA-01555 means the rollback/undo records needed by the reader were overwritten by other writers. Do not manufacture the failure by shrinking undo or generating uncontrolled churn on a shared system. In a dedicated test environment you can observe it naturally; in production, diagnose undo generation, space, tuned retention, long-query duration, and retention requirements.

7. Use historical data to repair current state explicitly

When only a few rows are wrong, querying the old image and applying a controlled current DML can be safer than rewinding the entire table. Verify the candidate row set first, preserve an audit trail, and let constraints/triggers operate according to the current schema.

MERGE INTO servicehub_flash_case curUSING (  SELECT case_id,status_code,amount  FROM servicehub_flash_case AS OF SCN :good_scn  WHERE case_id=1) oldON (cur.case_id=old.case_id)WHEN MATCHED THEN UPDATE SET  cur.status_code=old.status_code,  cur.amount=old.amount;SELECT case_id,status_code,amountFROM servicehub_flash_caseWHERE case_id=1;-- OPEN | 125ROLLBACK;

The ROLLBACK keeps the lesson non-destructive after proving the repair shape. In an incident, commit only after business/constraint verification.

8. Cleanup

DROP TABLE servicehub_flash_case PURGE;

9. Production judgment

Use Flashback Query for investigation, comparison, selective recovery, and historical reporting inside the retained window. Capture exact SCNs around risky changes when possible. Monitor undo space, tuned retention, long queries, and ORA-01555 counts. Flashback Query does not protect against lost datafiles, destroyed undo, lost database hosts, or retention windows shorter than the incident discovery time.

No restart, pack, or special COMPATIBLE change is required for ordinary Flashback Query in the 26ai baseline. Lesson 2 extends one historical image into a row-version timeline and then separates Free Version Query from the more privileged/licensed Flashback Transaction Query.

Authoritative references

• Using Oracle Flashback Technology — AS OF, privileges and Flashback Query guidance
• SELECT — flashback query clause
• SCN_TO_TIMESTAMP — SCN/time mapping precision and retention
• Managing Undo — undo retention/guarantee mechanics
• ORA-01555 — snapshot-too-old cause