Oracle Flashback: My Favorite Hidden Gem for Data Recovery

by
“AI-generated image of a dog looking devastated after accidentally deleting a database”

In my day-to-day work, Oracle is the main DBMS I use. Over time, I’ve come to really appreciate one particular feature—especially when things go wrong. It’s called Flashback, and it’s saved me from panic more times than I’d like to admit.

I’m not sure if other DBMS platforms have something similar, but in this post, I want to focus on how Oracle handles it.

So, What is Flashback?

Flashback lets you query data “as it was” at a specific point in time. For example, if you accidentally delete rows and already committed the transaction—yes, that heart-sinking moment—Flashback can help you bring them back.

⚠️ Just note: Flashback can recover data deleted via DELETE, but not if the data was removed using TRUNCATE, or if the table was dropped and purged. Those operations bypass the undo logs entirely.

Using AS OF TIMESTAMP, you can go back in time and view the data as it existed just minutes or even hours ago, depending on your system’s configuration.

Reference: https://docs.oracle.com/en/database/oracle/oracle-database/23/adfns/flashback.html

When Can You Use Flashback?

There are a few conditions your Oracle system needs to meet for Flashback Query to work properly:

  1. Undo Management must be set to AUTO UNDO_MANAGEMENT = AUTO
  2. Undo Retention must be configured
    This determines how far back in time you can go. For example: ALTER SYSTEM SET UNDO_RETENTION = 1200;
  3. Sufficient undo tablespace must be available
    Even if UNDO_RETENTION is set, the system might reclaim undo space earlier if it’s under pressure, so retention isn’t guaranteed unless you configure RETENTION GUARANTEE.

⚠️ You don’t need special permissions like DBMS_FLASHBACK package access to run Flashback Query with AS OF TIMESTAMP. That package is only needed for specific programmatic flashback operations (e.g., enabling session-level flashback).

Also, keep in mind:

  • Flashback only works for data stored in the local database.
  • You can’t use Flashback Query on tables accessed via a database link.
  • If a table’s structure has changed significantly (e.g., columns added or constraints modified), Flashback Query may not work reliably.

In my case, I’m not a DBA, but the good news is that Flashback was already enabled in my company’s Oracle setup—no extra steps required.

How to Use Flashback

Here’s a basic example of how you can query a table as it existed 5 minutes ago:

SQL
SELECT *
FROM MY_TABLE
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE)
WHERE MY_COLUMN = 'AAA';

You can change the time unit to SECOND, HOUR, or DAY depending on how far back you want to go.

Here’s how it might look for a stored procedure:

SQL
SELECT *
FROM DBA_SOURCE
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE)
WHERE NAME = 'MY_PROCEDURE';

Tip: Undo a Delete

If you accidentally deleted something and need to restore it quickly, you can do something like this:

SQL
DELETE FROM MY_TABLE WHERE MY_COLUMN = 'AAA';

INSERT INTO MY_TABLE
SELECT *
FROM MY_TABLE AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '5' MINUTE)
WHERE MY_COLUMN = 'AAA';

It’s super useful in emergencies—but remember, this doesn’t work when querying tables over a database link.

Final Thoughts

I’ve made a habit of memorizing this piece of magic:

SQL
AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '5' MINUTE)

Leave a Comment