Oracle DBA Lesson 81 Part 2 — Recover a Table Without Rewinding the Entire Database

Flashback Table returns the current data in named tables to an earlier state. It is useful when a committed mistake affects a bounded set of tables and the necessary undo history remains available. Start by inspecting the earlier state and identifying everything that would be reversed.

Oracle DBA Lesson 81 Part 2 — Recover a Table Without Rewinding the Entire Database

Match the statement to the goal

Flashback Query (SELECT ... AS OF SCN) retrieves an earlier committed version for inspection. FLASHBACK TABLE ... TO SCN changes current table data. Recovering a dropped table with TO BEFORE DROP uses the recycle bin and has different prerequisites.

Choose the recovery scope

Flashback Table restores all data in each named table to the target. Changes after that target are reversed, including legitimate later inserts, updates, and deletes. If only a few rows need correction while later work must survive, investigate a targeted repair instead. Prepare the correction and its validation against the actual incident.

Application relationships determine which tables belong in the recovery set. An order whose key stays 1 shows the difference between one table and the whole set:

State	Header total	Sum of line amounts	Application totals agree?
Recorded target	100	100	Yes
Later committed state	120	120	Yes
Only the header returned to the target	100	120	No
Header and lines returned to that same target	100	100	Yes

A foreign key connecting line rows to order 1 can remain valid in the inconsistent third state. It checks the referenced key. It does not enforce this cross-table sum rule. Inspect both declared constraints and application rules. Dependencies outside the named recovery set, including reports, caches, messages, and external side effects, may also need reconciliation. See recovery and constraint relationships.

Prerequisites and operational effects

• Undo and target. Required undo must extend back to the chosen SCN. A saved number identifies the target. It does not retain undo. Automatic undo management is required. Space, workload, and retention behavior affect history availability.
• Table structure. Intervening operations such as moving or truncating a table, modifying or dropping a column, or adding a constraint can block recovery across that change. Review the documented restrictions for the actual object and release.
• Row movement. Enable it on every table named in an SCN-based Flashback Table statement. Physical ROWID values are not preserved. Applications that depend on stored physical row addresses are unsuitable until that dependency is resolved. A business key can continue to identify the logical row.
• Privileges. Oracle documents FLASHBACK on the table, or FLASHBACK ANY TABLE, plus READ or SELECT, and INSERT, DELETE, and ALTER object privileges. Table owners have their own object privileges. Capturing an SCN through DBMS_FLASHBACK also requires EXECUTE on the package. Use the required object access rather than granting broad system privileges for convenience.
• Availability. The Oracle 19c licensing matrix lists Flashback Table as available in Enterprise Edition and unavailable in Standard Edition 2. Confirm the exact offering before use.
• Locking and transactions. Oracle acquires exclusive DML locks on the listed tables. Plan the affected write interruption, finish existing work, and keep application writers stopped through recovery and validation. The listed tables are recovered in one transaction. All revert, or the statement fails.
• Successful recovery. A subsequent ROLLBACK cannot cancel it. Oracle recommends recording the current SCN before recovery. A later Flashback Table to that pre-recovery point is another recovery operation, dependent on available history and all applicable conditions.
• Triggers. Enabled table triggers are disabled during the operation by default and returned to their prior state afterward. ENABLE TRIGGERS changes this behavior and can introduce side effects. Review that clause deliberately.
• Other object state. Current indexes reflect the target data, but dropped indexes are not recreated and optimizer statistics are not returned to their earlier values. Review the required application and object state after recovery.

See FLASHBACK TABLE and availability by offering.

Return the table to the saved SCN

The statements below use flashback_demo, an ordinary heap table. Confirm the connected user and container first. Creation needs CREATE TABLE and a finite quota on the assigned permanent tablespace.

SHOW USER
SHOW CON_NAME

Create the table and enable row movement

CREATE TABLE flashback_demo (
    id   NUMBER PRIMARY KEY,
    note VARCHAR2(30)
);

INSERT INTO flashback_demo VALUES (1, 'BEFORE');
INSERT INTO flashback_demo VALUES (2, 'KEEP');
COMMIT;

ALTER TABLE flashback_demo ENABLE ROW MOVEMENT;

SELECT table_name, row_movement
FROM user_tables
WHERE table_name = 'FLASHBACK_DEMO';

SELECT id, note FROM flashback_demo ORDER BY id;

The baseline rows are:

ID	NOTE
1	BEFORE
2	KEEP

Enable row movement and finish setup before capturing the target. Oracle's SQL reference example notes SCN-to-time mapping propagation for newly created tables and recommends a minimum five-minute wait in that example. Prepare the object in advance and allow that propagation interval before capturing the good SCN and making the unwanted change. Do not create, truncate, or reshape the table after choosing the target.

Save the target SCN

Save a real current SCN as a numeric SQL*Plus substitution value, after the baseline commit:

SET DEFINE ON
UNDEFINE good_scn
COLUMN target_text NEW_VALUE good_scn NOPRINT

SELECT TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER,
               'FM99999999999999999999') AS target_text
FROM dual;

DEFINE good_scn

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER obtains the current SCN from this database. TO_CHAR formats it as decimal digits. COLUMN ... NEW_VALUE assigns the selected text to the SQL*Plus variable good_scn. SQL*Plus later substitutes &good_scn into the recovery command as a numeric literal. This is a client substitution variable, not a server bind in DDL. Save the actual value and keep the same SQL*Plus session. Never substitute untrusted free text, and never copy an SCN from another database. See NEW_VALUE and SCN capture and package access.

Commit the unwanted change

UPDATE flashback_demo
SET note = 'UNWANTED'
WHERE id = 1;
COMMIT;

SELECT id, note FROM flashback_demo ORDER BY id;

The current rows are:

ID	NOTE
1	UNWANTED
2	KEEP

Read the historical rows

Inspect the selected historical target before changing the table:

SELECT id, note
FROM flashback_demo AS OF SCN &good_scn
ORDER BY id;

Expected target rows are 1 / BEFORE and 2 / KEEP, provided the required history is available. Stop if the target, the row set, or the expected recovery scope is wrong. An unsuccessful historical query leaves the earlier data unverified. See historical inspection and undo prerequisites.

Flash the table back

Coordinate writers and finish outstanding transactions before running recovery. Record the current SCN immediately before it:

UNDEFINE pre_recovery_scn
COLUMN pre_text NEW_VALUE pre_recovery_scn NOPRINT

SELECT TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER,
               'FM99999999999999999999') AS pre_text
FROM dual;

DEFINE pre_recovery_scn

FLASHBACK TABLE flashback_demo TO SCN &good_scn;

SELECT id, note FROM flashback_demo ORDER BY id;

The current query should now return:

ID	NOTE
1	BEFORE
2	KEEP

Check the complete expected row set and values. The unchanged second row is part of that check. A successful command message alone does not establish that the application is ready. The captured pre-recovery SCN is a possible target for a later recovery operation, subject to the remaining undo and the other conditions. It is not a guaranteed backout.

Recover related tables together

When a header and its lines both need the same earlier state, name both tables in one statement:

FLASHBACK TABLE order_header, order_line TO SCN &good_scn;

order_header and order_line are a separate application example. They are not created with flashback_demo, and that table's saved SCN is not their recovery target. Record a fresh shared target after both baseline commits, enable row movement on both, review outside dependencies, inspect both historical states, and preserve the pre-recovery point. All prerequisites apply to each named table. A constraint failure on any table causes the whole statement to fail. Do not disable constraints to force the statement through. See prerequisites and related tables.

Check the recovered state

Before resuming writes, verify recovered values and row sets, related-table totals, constraint state, trigger state, indexes, and the important application operations. Reconcile external effects separately where needed. If the necessary history is gone, or structural restrictions prevent table flashback, choose a tested backup-based table recovery or a broader recovery method appropriate to the incident, and assess the effect on valid work.

Recap

Flashback Table returns all data in each named table to one earlier SCN and reverses later changes in those tables, including valid work. Enable row movement, confirm undo and structure, inspect the historical rows, preserve a pre-recovery SCN, and check related tables before writers resume.

Quiz

1. What happens to valid changes made after the target in a table being flashed back?

Oracle retains them automatically. They are reversed with the other later changes in that table. Only uncommitted changes are reversed. They move into another table.

2. Can enabling row movement recreate overwritten undo?

Yes, because ROWIDs contain the old values. Yes, after a commit. No. Movement permission and history availability are separate prerequisites. Only if both related tables are named.

3. The header total is 100 and the line total is 120 after recovering only the header. The foreign key is valid. What next?

Resume writes because the foreign key passed. Treat 120 as a display-format problem. Drop the foreign key. Review the application recovery set and reconcile the totals at the intended target.

4. One table in a multi-table Flashback Table statement fails a constraint check. What happens to the statement?

The successful tables remain recovered and the rest are skipped. The whole statement fails. The listed tables are recovered as one transaction. Oracle disables the constraint automatically. Oracle changes the target SCN.

5. How can a successful Flashback Table be canceled afterward?

A normal ROLLBACK cancels it. Closing SQL*Plus cancels it. A further recovery operation may return to a recorded pre-recovery SCN if all required history and conditions remain available. Disabling row movement restores the previous data.

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily