Oracle Flashback Technology for Developers

Undo Your Mistakes Without a DBA

Oracle’s Flashback Technology is a family of features that let developers and DBAs view or restore data as it existed at a previous point in time. For developers, the most practical Flashback features are Flashback Query, Flashback Version Query, and Flashback Table, all of which can be used without DBA privileges on most systems.

Flashback Query: See Past Data

Query a table as it existed at a specific point in time using the AS OF clause:

-- See what the row looked like 30 minutes ago
SELECT employee_id, salary, job_id
  FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE)
 WHERE employee_id = 101;

-- Compare current vs previous state
SELECT curr.employee_id,
       curr.salary AS current_salary,
       prev.salary AS previous_salary,
       curr.salary - prev.salary AS change
  FROM employees curr
  JOIN employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR) prev
    ON curr.employee_id = prev.employee_id
 WHERE curr.salary != prev.salary;

Flashback Version Query: Full Change History

See every version of a row between two points in time, including who changed it and what transaction made the change:

SELECT employee_id, salary, job_id,
       VERSIONS_STARTTIME AS changed_at,
       VERSIONS_OPERATION AS operation,
       VERSIONS_XID AS transaction_id
  FROM employees
       VERSIONS BETWEEN TIMESTAMP
           (SYSTIMESTAMP - INTERVAL '24' HOUR) AND SYSTIMESTAMP
 WHERE employee_id = 101
 ORDER BY VERSIONS_STARTTIME;

VERSIONS_OPERATION shows I for insert, U for update, and D for delete. This is invaluable for debugging data issues in production without maintaining your own audit trail.

Flashback Table: Undo Accidental Changes

-- Restore a table to a previous state
ALTER TABLE employees ENABLE ROW MOVEMENT;

FLASHBACK TABLE employees
    TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' HOUR);

-- Or restore specific rows using INSERT from a Flashback Query
INSERT INTO employees
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' HOUR)
 WHERE employee_id = 101;

Limitations

Flashback depends on undo data, which has a limited retention period configured by the DBA (the UNDO_RETENTION parameter, typically 15 minutes to 24 hours). If you try to flash back beyond the undo retention window, you get an ORA-01555 snapshot too old error. DDL operations like TRUNCATE and DROP bypass undo and cannot be flashed back using these features (Flashback Drop, which recovers dropped tables from the recycle bin, is a separate feature).