A committed update can leave the current value wrong while its earlier committed version is still available through undo. Oracle Flashback Query lets you inspect that earlier version with SELECT ... AS OF SCN, then compare it with current data. The query itself leaves current data unchanged.
Oracle DBA Lesson 81 Part 1 — Inspect Data Before an Unwanted Change
Compare the recorded SCN with the current row
Row MARKER_ID = 1 in RECOVERY_MARKER starts with the committed value BASELINE. A later update commits CHANGED. With the required undo still available, the two queries return these results:
| Query target | MARKER_ID | MARKER_TEXT |
|---|---|---|
| Recorded SCN before the update | 1 | BASELINE |
| Current state after the update | 1 | CHANGED |
The historical query establishes the earlier value. A correction is a further operation, and it has to account for other related data. Record the actual original value from the row. A reused table may contain something other than BASELINE.
Use an SCN from the same database
A system change number (SCN) is Oracle's internal ordering value for database changes. A recorded SCN supplies a definite query target from that database. It is not a time of day, and a number copied from another database is not a usable substitute.
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER returns the current SCN as an Oracle NUMBER. A SQL*Plus bind retains it for later statements in the same session. Saving the number does not preserve or reserve the undo needed to reconstruct that state. Keep an external record when the investigation needs to outlive the session. See GET_SYSTEM_CHANGE_NUMBER and package access.
Capture the target after the baseline transaction has committed and before the unwanted update. A target captured after the unwanted commit includes that change. The comparison also depends on no other session changing the row or its table structure between these steps.
Read the earlier SCN and the current row
Read the original committed value
RECOVERY_MARKER has numeric MARKER_ID and character MARKER_TEXT. The statements follow the single row MARKER_ID = 1, whose baseline is already committed.
SELECT marker_id, marker_text
FROM recovery_marker
WHERE marker_id = 1;Record the exact value, including whether it is null. Where MARKER_TEXT fits in 4,000 characters, a SQL*Plus bind retains that value, including null and embedded quotes, without retyping it:
VARIABLE original_marker_text VARCHAR2(4000)
BEGIN
SELECT marker_text INTO :original_marker_text
FROM recovery_marker
WHERE marker_id = 1;
END;
/The lookup expects that single row.
Capture a fresh SCN
VARIABLE before_scn NUMBER
EXEC :before_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
PRINT before_scnVARIABLE declares a client bind. EXEC calls the function and assigns the returned number. The colon references the bind. PRINT shows the number so every digit can be recorded with the database and container. This call neither copies the table nor enables a session-wide flashback mode.
Commit the unwanted change
UPDATE recovery_marker
SET marker_text = 'CHANGED'
WHERE marker_id = 1;
COMMIT;Verify that exactly the intended row was affected. The commit completes this transaction. A later ROLLBACK cannot cancel that completed update.
SELECT marker_id, marker_text
FROM recovery_marker
WHERE marker_id = 1;Row 1 now contains CHANGED.
Read the earlier committed state
SELECT marker_id, marker_text
FROM recovery_marker
AS OF SCN :before_scn
WHERE marker_id = 1;The flashback clause follows the table reference. It asks for committed data at the recorded SCN. The selected columns and key predicate stay the same, so the result compares directly with the current row. With the required history and a compatible table structure, this query returns BASELINE. See the flashback query clause and historical queries and SCN targets.
Read the current row again
SELECT marker_id, marker_text
FROM recovery_marker
WHERE marker_id = 1;It still returns CHANGED. The historical SELECT has not written the earlier value back into the table. This query uses an AS OF clause on one table reference, not DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER session mode.
How undo supplies the earlier result
An update generates undo information describing how to reverse its effects. In automatic undo management, Oracle manages undo segments in an undo tablespace backed by physical data files on disk. Relevant data and undo blocks can be cached in the database buffer cache in the SGA.
To provide an earlier consistent view, Oracle can copy current data blocks into read-consistent buffers and apply the required undo to those copies. It reconstructs the committed data appropriate to the requested SCN and returns the query result. It may need multiple undo records for several intervening changes. Undo is not a complete separately stored copy of every old table.
A simplified single-row reconstruction retains CHANGED in the current block and reconstructs BASELINE in a query copy. Applying undo to that consistent view is not a user transaction that restores current table data. That shows the logical data dependency. Actual cached blocks need not be fetched from disk on every query. This is not a backup restore or a Flashback Database operation. See consistent-read reconstruction.
When the earlier version cannot be read
Undo reuse
Once required undo is overwritten, an older view may no longer be reconstructible. An error such as ORA-01555: snapshot too old signals missing history for the requested read. It does not establish the value that existed at the target. Saving the SCN does not stop reuse. Undo capacity, workload, and retention behavior matter. UNDO_RETENTION alone is not a guarantee under space pressure. A retention guarantee changes overwrite behavior and can cause new transactions to fail when sufficient undo space cannot be obtained. See undo storage, reuse, and retention.
Structural DDL
Truncation and structural table changes, such as dropping or modifying a column or moving the table, can invalidate historical access across that change. Oracle documents ORA-01466 for querying across such changes. Not every ALTER TABLE action has this effect. Storage-attribute changes such as PCTFREE are documented separately. Check the actual intervening operation. See DDL boundaries for historical queries.
Wrong target or context
The SCN must come from the intended database and must have been captured before the unwanted committed change. An invalid SCN, a wrong table or container, missing privileges, and missing undo are different conditions. An error alone is not proof that a particular old row never existed. A successful query that returns no row has to be read against the chosen target and key.
Backup and recovery
Flashback Query relies on available data and history. It does not replace backups when the needed blocks or undo are unavailable. Table recovery and database flashback are separate choices.
Privileges and separate flashback features
An owner can query its own table. For another schema's object, Oracle documents FLASHBACK plus READ or SELECT on that object for a historical query. Reading the current row and running the update need their own privileges. Calling DBMS_FLASHBACK requires EXECUTE on the package. The historical query does not require FLASHBACK ANY TABLE. See historical-query privileges.
Flashback Query, Flashback Table, Flashback Transaction Query, and Flashback Database are distinct features. Several of the latter operations are restricted by offering in the licensing table. A historical SELECT does not enable those recovery features, and it does not establish their eligibility. See offering-specific feature conditions.
Recap
Record an SCN after the baseline commit and before the unwanted change, compare AS OF SCN with the current row, and treat undo availability and structural DDL as limits on that read. The query shows the earlier committed value and leaves current data unchanged.
No comments:
Post a Comment