Oracle Flashback Technology: Point-in-Time Recovery and Queries

What Oracle Flashback provides

Oracle Flashback is a family of features that use the automatic undo data maintained by Oracle to:

• Query past versions of data (Flashback Query)
• See all versions of a row over time (Flashback Versions Query)
• Undo DML mistakes (Flashback Table)
• Restore dropped tables (Flashback Drop)
• Rewind entire databases to a past SCN (Flashback Database)

All without backups, exports, or significant downtime.

Flashback Query: SELECT AS OF

Query data as it existed at a past point in time:

-- As of a specific timestamp:
SELECT * FROM orders
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' HOUR)
WHERE status = 'CANCELLED';

-- As of a System Change Number (SCN):
SELECT * FROM orders AS OF SCN 12345678
WHERE order_date = TRUNC(SYSDATE);

Limited by UNDO_RETENTION parameter (typically 15 minutes to several hours). Extend it for more history window.

Flashback Versions Query: row history

See all versions of specific rows over a time range:

SELECT versions_starttime, versions_endtime, versions_operation,
       employee_id, salary
FROM employees
VERSIONS BETWEEN TIMESTAMP 
  (SYSTIMESTAMP - INTERVAL '1' HOUR)
  AND SYSTIMESTAMP
WHERE employee_id = 101
ORDER BY versions_starttime;

versions_operation shows I (insert), U (update), D (delete).

Flashback Table: undoing mistakes

Roll back an entire table to a past point:

-- First enable row movement (required):
ALTER TABLE orders ENABLE ROW MOVEMENT;

-- Then flashback:
FLASHBACK TABLE orders TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE);

Flashback Drop: recovering dropped tables

Oracle moves dropped tables to the Recycle Bin:

-- See what's in the recycle bin:
SELECT object_name, original_name, droptime FROM recyclebin;

-- Restore:
FLASHBACK TABLE orders TO BEFORE DROP;

-- Or restore with new name:
FLASHBACK TABLE orders TO BEFORE DROP RENAME TO orders_recovered;

Practical use in FBDI error recovery

After a failed FBDI import that partially corrupted staging data, Flashback Query lets you see the staging table’s pre-import state, identify which rows were modified, and restore them without regenerating the entire source file.