Flashback

Flashback is Oracle's set of features for recovering and querying past states of your data — without restoring from backup. You can drop a table by mistake and bring it back in seconds; you can query a table "as it looked yesterday at 3 PM"; you can audit who changed what and when.

These features use the database's existing undo and redo data, so they work on data that's already in the database — they're not a substitute for backups, but they're a much faster recovery path for many common mistakes.

The Flashback Family

Oracle ships several Flashback features that solve different problems:

The first five work with existing undo data and have practical limits (undo retention is usually hours, not days). Flashback Data Archive is the long-term option for tables that need compliance-grade history.

Part 1 — Flashback Drop and the Recycle Bin

What Happens When You DROP TABLE

When you run DROP TABLE employees, Oracle does not immediately delete the table. By default it renames the table and moves it into your recycle bin. The space is freed for reuse but the data is recoverable.

-- Drop a table
DROP TABLE old_employees;

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

Result:

The renamed object lives in the recycle bin until it's purged or until Oracle reclaims its space for new data.

Recovering with FLASHBACK TABLE ... TO BEFORE DROP

FLASHBACK TABLE old_employees TO BEFORE DROP;

The table reappears with its original name and data. If you need a different name:

FLASHBACK TABLE old_employees TO BEFORE DROP RENAME TO old_emp_recovered;

If multiple objects with the same original name exist (you dropped and recreated the table several times), use the bin name explicitly:

FLASHBACK TABLE "BIN$NyP0kZ4...gZA==$0" TO BEFORE DROP;

Purging the Recycle Bin

-- Permanently delete a specific dropped table (irrecoverable)
PURGE TABLE old_employees;

-- Empty your entire recycle bin
PURGE RECYCLEBIN;

-- DBA: purge the system-wide recycle bin
PURGE DBA_RECYCLEBIN;

To bypass the recycle bin and drop a table immediately:

DROP TABLE old_employees PURGE;

Limitations

Flashback Drop has limits:

• Only works on non-SYS tables in a locally-managed tablespace
• Doesn't recover tables dropped with PURGE
• Doesn't preserve foreign keys (you must recreate them)
• Indexes and triggers come back, but bitmap join indexes do not

Part 2 — Flashback Query

Querying the Past

The AS OF clause queries a table at a specific point in the past:

-- See employees as they existed an hour ago
SELECT employee_id, first_name, salary
FROM   employees
       AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR;

You can use a TIMESTAMP (wall-clock time) or an SCN (system change number — Oracle's internal monotonic time):

-- By SCN
AS OF SCN 1234567

-- By specific timestamp
AS OF TIMESTAMP TO_TIMESTAMP('2024-03-15 14:00:00', 'YYYY-MM-DD HH24:MI:SS')

-- Relative
AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '30' MINUTE

Comparing Past and Present

Combine a flashback query with the current state to see what changed:

-- Rows that have been deleted in the past hour
SELECT * FROM employees AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR
MINUS
SELECT * FROM employees;

-- Rows where salary changed in the past hour
SELECT old.employee_id,
       old.salary AS old_salary,
       cur.salary AS new_salary
FROM   employees AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR old
JOIN   employees cur ON old.employee_id = cur.employee_id
WHERE  old.salary <> cur.salary;

Recovering Deleted Data

When someone accidentally deletes rows, flashback query is often the fastest recovery:

-- 1. Identify the deleted rows
SELECT * FROM employees AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '15' MINUTE
MINUS
SELECT * FROM employees;

-- 2. Re-insert them
INSERT INTO employees
SELECT * FROM employees AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '15' MINUTE
MINUS
SELECT * FROM employees;

This recovers all rows that existed 15 minutes ago but no longer exist now. Much faster than restoring from backup.

How Far Back Can You Go?

Flashback Query depends on undo retention — how long Oracle keeps undo data. The DBA configures this:

SELECT * FROM v$undostat;                       -- usage statistics
SELECT name, value FROM v$parameter
WHERE  name IN ('undo_retention', 'undo_management');

Typical default: 900 seconds (15 minutes). Production databases often set this to a few hours. Anything older than undo_retention is best-effort — Oracle will keep it if there's room, but new transactions can overwrite older undo.

If a flashback fails:

ORA-01555: snapshot too old: rollback segment number X with name "X" too small

The undo you need has been overwritten. The data isn't recoverable from undo at this point — you'll need a backup.

Part 3 — Flashback Version Query

VERSIONS BETWEEN shows you every committed state a row passed through between two points in time:

SELECT employee_id,
       salary,
       VERSIONS_STARTTIME,
       VERSIONS_ENDTIME,
       VERSIONS_OPERATION
FROM   employees
       VERSIONS BETWEEN TIMESTAMP
         (SYSTIMESTAMP - INTERVAL '1' DAY)
         AND SYSTIMESTAMP
WHERE  employee_id = 100;

Result:

Three rows = three versions of this employee's salary. The VERSIONS_OPERATION is I (insert), U (update), or D (delete).

VERSIONS_STARTTIME = NULL means "this version existed at the start of the time window". VERSIONS_ENDTIME = NULL means "this version is the current state".

Pseudocolumns available in version queries:

• VERSIONS_STARTSCN, VERSIONS_ENDSCN — SCNs
• VERSIONS_STARTTIME, VERSIONS_ENDTIME — timestamps
• VERSIONS_OPERATION — I/U/D
• VERSIONS_XID — transaction ID that produced this version (link to FLASHBACK_TRANSACTION_QUERY)

Part 4 — Flashback Transaction Query

Once you have a VERSIONS_XID (transaction ID), you can look up details of that transaction:

SELECT xid, operation, table_name, undo_sql
FROM   FLASHBACK_TRANSACTION_QUERY
WHERE  xid = HEXTORAW('06000A0023040000');

Result:

The undo_sql column gives you the exact SQL that would reverse this transaction — invaluable for forensic recovery.

Permissions: requires SELECT ANY TRANSACTION privilege (DBA only).

Part 5 — Flashback Table (Rewind)

Unlike Flashback Query (read-only), FLASHBACK TABLE ... TO TIMESTAMP actually modifies the table to undo recent changes:

-- Required setup: enable row movement
ALTER TABLE employees ENABLE ROW MOVEMENT;

-- Rewind the table to 1 hour ago
FLASHBACK TABLE employees TO TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR;

After this, the table looks like it did an hour ago. The changes are undone, not deleted from history — you could flashback "forward" again if you change your mind, as long as undo retention permits.

ENABLE ROW MOVEMENT is required because flashback may need to update ROWIDs.

-- Disable row movement again if desired
ALTER TABLE employees DISABLE ROW MOVEMENT;

Use with care. Other users may have made changes you don't want to lose. Flashback Table is destructive in the sense that it rolls back all changes since the chosen point.

Part 6 — Flashback Data Archive (Total Recall)

For data that needs to be retained for months or years, undo retention is impractical. Flashback Data Archive (FDA), historically called "Total Recall", stores history in dedicated archive tablespaces with configurable retention.

-- DBA: create the archive
CREATE FLASHBACK ARCHIVE compliance_archive
TABLESPACE archive_ts
RETENTION 7 YEAR;

-- Attach a table to the archive
ALTER TABLE financial_transactions FLASHBACK ARCHIVE compliance_archive;

Now VERSIONS BETWEEN and AS OF TIMESTAMP work for the table going back 7 years.

-- Query as it was a year ago
SELECT * FROM financial_transactions
       AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' YEAR;

FDA stores its history independently of undo, so it survives backups, retention policy changes, and ordinary undo cycling. The trade-off is disk space — every change is logged.

Common use cases:

• Regulatory compliance (SOX, GDPR right-to-history)
• Audit trails for financial records
• Change tracking on configuration tables

FDA Limitations

• Cannot flashback the archived table itself (only query)
• Some DDL operations require special handling (ALTER TABLE ... NO FLASHBACK ARCHIVE temporarily)
• Comes with licensing implications (Advanced Compression option for some Oracle editions)

Permissions and Setup

Grant the user-level privilege:

GRANT FLASHBACK ON employees TO hr_user;
GRANT FLASHBACK ANY TABLE TO super_user;

Worked Example — Recovering from "Oops, I Deleted All Salaries"

Friday 4:55 PM. A developer runs:

UPDATE employees SET salary = 0;
COMMIT;

It's a hot system; you can't restore from backup without 4 hours of downtime. Flashback to the rescue.

Step 1 — Confirm the damage:

SELECT employee_id, first_name, salary
FROM   employees
WHERE  salary = 0;
-- Returns 107 rows (everyone)

Step 2 — Check that the data still exists in undo:

SELECT employee_id, salary
FROM   employees
       AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE
WHERE  ROWNUM <= 5;
-- Returns 5 rows with real salaries

Step 3 — Restore the salaries with an UPDATE driven by flashback:

UPDATE employees cur
SET    cur.salary = (
  SELECT old.salary
  FROM   employees AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE old
  WHERE  old.employee_id = cur.employee_id
);
COMMIT;

Salaries restored in seconds with no downtime. Total elapsed time from "oh no" to "fixed" is under five minutes.

Step 4 — Lesson learned:

ALTER USER developer QUOTA 0 ON USERS;   -- jk

The real fix: change management, code review, and a WHERE clause review checklist.

Common Errors

Interview Corner

FLASHBACK TABLE employees TO BEFORE DROP;

Related Topics

• DML Commands — flashback recovers from accidental UPDATE/DELETE
• DDL Commands — flashback drop recovers from accidental DROP TABLE
• Transactions & Locks — undo retention drives flashback availability
• Performance — long flashback queries on large tables can be slow; tune accordingly
• Constraints — FK constraints affect what flashback can/cannot restore