Reading Past Row Versions with Oracle Flashback Query

Problem and useful answer

You committed an UPDATE or DELETE that changed or removed rows, and you need the previous values back. You do not want to perform a point-in-time recovery or restore a backup just to inspect a few rows.

Useful answer: Oracle Flashback Query lets you SELECT data as it existed at a previous system change number (SCN) or timestamp, using the AS OF clause. The database reads the earlier row version from undo segments, so no backup restore is required. This works only within the undo retention window defined by UNDO_RETENTION.

How Flashback Query reads history

Oracle stores previous versions of changed rows in undo segments. When you issue SELECT ... AS OF SCN or SELECT ... AS OF TIMESTAMP, Oracle reconstructs the row as it appeared at that point by applying undo records to the current image. AS OF is read-only: it returns historical data but does not modify the table.

Key terms:

• SCN: system change number, a database-internal marker that advances with each committed transaction.
• Undo retention: the minimum number of seconds Oracle tries to keep undo information, controlled by the UNDO_RETENTION initialization parameter.
• ORA-01555: snapshot too old: the error raised when the requested point in time is no longer available in undo.

Worked example: compare before and after an update

Run these commands in a SQL client such as SQL*Plus or SQL Developer, connected as a user with SELECT on the target table. If the table belongs to another schema, you also need the FLASHBACK ANY TABLE system privilege or the object-level FLASHBACK privilege.

Assumptions: Oracle Database 19c or later, an online undo tablespace, and a non-system table.

Step 1: Create a scratch table and capture the starting SCN

-- Run as the table owner or a user with SELECT on the table
CREATE TABLE flashback_test (
    employee_id   NUMBER PRIMARY KEY,
    salary        NUMBER,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO flashback_test (employee_id, salary) VALUES (101, 5000);
COMMIT;

-- Capture the SCN before the change
SELECT current_scn FROM v$database;

Expected check: CURRENT_SCN returns a numeric value. Record this placeholder as <start_scn> for the next step.

Step 2: Change the data and commit

UPDATE flashback_test
SET salary = 99999
WHERE employee_id = 101;

COMMIT;

Step 3: Query the historical row using AS OF SCN

-- Replace <start_scn> with the value captured in Step 1
SELECT employee_id, salary, updated_at
FROM flashback_test AS OF SCN <start_scn>;

Expected check: the query returns salary = 5000, the value before the UPDATE. The current table still shows 99999.

Step 4: Query using AS OF TIMESTAMP

SELECT employee_id, salary
FROM flashback_test AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);

Expected check: if five minutes is within the undo retention window, the row returns the pre-update salary. If the window is too short, Oracle raises ORA-01555: snapshot too old.

Limits and common mistakes

Issue	Behavior	Practical check
Undo retention window	Flashback Query cannot read beyond the time Oracle retains undo. UNDO_RETENTION is a target, not a guarantee, especially under heavy DML or a small undo tablespace.	Run SHOW PARAMETER UNDO_RETENTION; and review V$UNDOSTAT for undo usage and failed query attempts.
ORA-01555	Raised when the requested SCN or timestamp is older than available undo.	Increase undo tablespace size or UNDO_RETENTION with appropriate privileges, or accept that the history is unavailable from undo.
DDL changes	Flashback Query does not preserve old table definitions. Dropping or adding columns after the target time can cause errors or unexpected nulls.	Avoid using AS OF across schema changes; verify the table structure matches the target period.
Read-only	AS OF returns historical rows but does not write them back to the table.	Use the query output to manually re-insert or update rows if you need to restore data.
Not a backup substitute	Undo is recycled; long-term history requires backups, Flashback Database, or Flashback Data Archive.	Confirm your backup strategy covers retention needs beyond the undo window.

Verify the result

1. Run SHOW PARAMETER UNDO_RETENTION; to see the configured retention in seconds.
2. Run SELECT * FROM v$undostat; and check whether undo space is sufficient for your workload.
3. Repeat the Step 1–4 example with your own table. If the historical value appears, Flashback Query is functioning within the retention window.
4. If you receive ORA-01555, the requested point is outside the undo window; no amount of query tuning will recover it from undo alone.

When to use this instead of other options

Use Flashback Query for quick inspection of recently committed changes when the change falls inside the undo retention window. Use Flashback Table or point-in-time recovery for broader restoration, and use Flashback Data Archive when you need history retained longer than undo allows.

This feature is read-only and changes no state, so there is no rollback procedure for the query itself.