Master Oracle Flashback: Recover Data with Queries, Versions, and Transactions

Overview of Oracle Flashback Technology

Oracle introduced Flashback Query in 9i, allowing users to read data from rollback segments to recover erroneous DML operations. Starting with 10g, Oracle added Flashback Version Query, Flashback Transaction Query, Flashback Table, Flashback Drop, and Flashback Database, fundamentally changing database recovery by enabling row‑level and transaction‑level restoration via simple SQL statements.

Key Flashback Features

Flashback Query : View data as of a specific point in time using the AS OF clause.

Flashback Version Query : Retrieve all committed versions of a row within a time interval via the VERSIONS BETWEEN clause.

Flashback Transaction Query : Inspect changes made by a transaction.

Flashback Table : Restore an entire table to a previous state without affecting other objects.

Flashback Drop : Recover dropped tables and their dependent objects from the recycle bin.

Required Undo Tablespace Settings

To use Flashback features, the undo tablespace must be enabled for automatic management of rollback information. For Flashback Drop and Flashback Database, the recycle bin and Flash Recovery Area must also be enabled.

Relevant parameters include: UNDO_MANAGEMENT: Set to AUTO for automatic undo management. UNDO_TABLESPACE: Name of the undo tablespace. UNDO_RETENTION: Maximum retention time for undo data.

Flashback Query

Basic syntax:

SELECT column_name, ...
FROM table_name
[AS OF SCN|TIMESTAMP expression]
[WHERE condition];

Example workflow:

Set session date format and enable time tracking. ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'; Query current salary of employee 7844. SELECT empno, sal FROM scott.emp WHERE empno=7844; Update salary, commit, then use AS OF TIMESTAMP to view previous values at different moments.

Sample queries with results are illustrated by the following images:

Flashback Version Query

Syntax:

SELECT column_name, ...
FROM table_name
[VERSIONS BETWEEN SCN|TIMESTAMP MINVALUE AND MAXVALUE]
[AS OF SCN|TIMESTAMP expression]
WHERE condition;

This returns pseudo‑columns such as VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_XID, and VERSIONS_OPERATION to show the history of a row.

Example retrieving all versions of employee 7844’s salary between two timestamps:

SELECT versions_xid, versions_starttime, versions_endtime,
       versions_operation, sal
FROM scott.emp
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
WHERE empno = 7844
ORDER BY versions_starttime;

To restore a row to a previous state, combine the version query with an UPDATE ... SELECT ... AS OF TIMESTAMP statement.

Flashback Transaction Query

Provides transaction‑level visibility of changes:

SELECT operation, table_name, undo_sql
FROM FLASHBACK_TRANSACTION_QUERY
WHERE xid = HEXTORAW('01001B00A0050000');

Typically, you first use a version query to obtain the transaction ID, then query FLASHBACK_TRANSACTION_QUERY for detailed undo information.