Undoing Mistakes in Oracle with Flashback Query

You know when you make that epic blunder, run an UPDATE on a table without a WHERE clause, and you’re so confident that you even throw in a COMMIT right after? Yeah… it happens.

Or when a user deletes or modifies a bunch of records they really shouldn’t have touched? The first reaction is usually to call the DBA, ask to restore the last backup, and pray to RMAN. But honestly, it doesn’t always have to go that far.

There’s a much simpler — and in many cases, much easier — way out: Flashback Query.
Even though it’s a well-known feature, it’s worth revisiting. With it, Oracle can reconstruct the previous state of rows using data stored in the UNDO tablespace. In other words, it “rewinds time” by applying the inverse of the changes recorded there.

And the best part? As far as I know, this feature has been enabled by default for ages — so you’re unlikely to run into any issues using it.

Let’s get straight to the point. Below are two queries for comparison:
One normal query, showing current results, and another using Flashback Query to retrieve data from 30 minutes ago.

You can test this yourself by creating a table, inserting some data, noting the time you delete the records, and then running the SELECT for an earlier timestamp.

Example

--Normal query with current results
SELECT *
FROM ORDERS
WHERE ORDER_ID = 3487;

--Flashback query retrieving results from 30 minutes ago
SELECT *
FROM ORDERS AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE)
WHERE ORDER_ID = 3487;

Nice. Now let’s say you want to recover the records you deleted. That’s easy too.

Inserting data based on the state from 30 minutes ago

INSERT INTO ORDERS
SELECT *
FROM ORDERS AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE)
WHERE ORDER_ID = 3487;
COMMIT;

That’s it. Really — that’s all you need.

The article could end here. But if you’re a bit more curious, keep reading — the next part requires DBA privileges.

Checking the parameters

Alright, now let’s find out the theoretical retention time currently configured:

SHOW PARAMETER undo_retention;

It will show the value in seconds. In my case, it’s set to 86,400 seconds — that’s 1,440 minutes, or 24 hours. It’s important to note that this is a theoretical value. By default, if the database needs UNDO space, it can overwrite older undo data even before the retention period expires — unless you explicitly tell it to guarantee that retention.

To check that, run:

SELECT tablespace_name, retention FROM dba_tablespaces WHERE contents = 'UNDO';

✅ If it says GUARANTEE, the retention time will always be respected.
⚠️ If it says NOGUARANTEE, Oracle may overwrite the undo data early if it needs space.

Enforcing retention

If you want to make sure Flashback Query always works within the configured time limit:

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

I wouldn’t recommend doing this — because if Oracle must honor that retention time and runs out of UNDO space, the database will throw an error.

Adjusting the retention time

Here’s an example setting the retention time to 3,600 seconds (that’s 60 minutes):

ALTER SYSTEM SET undo_retention = 3600;

And that’s it, folks. I don’t recommend messing with these parameters, but I do recommend taking advantage of Flashback Query whenever you need to fix something simple.

Flashback Query is fast and practical — but it’s not a replacement for a proper backup and recovery strategy. Think of it as a lifesaver for those “small accidents.”