Oracle Flashback cheat sheet: undo bad changes with Flashback Query, Table, Drop and Database

#Pick the right tool

Someone deleted rows, ran an UPDATE without a WHERE, dropped a table or a release script went wrong. Before you reach for an RMAN restore, check whether Flashback can do it in minutes.

Flashback Query works in all editions. Flashback Table, Flashback Transaction Query and Flashback Database are Enterprise Edition features; check the licensing guide for your version.

#Find the point in time first

SELECT current_scn, SYSTIMESTAMP FROM v$database;
SELECT TIMESTAMP_TO_SCN(TIMESTAMP '2026-10-31 09:40:00') AS scn FROM dual;
SELECT SCN_TO_TIMESTAMP(48211730) AS ts FROM dual;

Work in SCNs when you can. Timestamp-to-SCN mapping is approximate (a few seconds), and an SCN is exact.

#Flashback Query: read old data

-- How the table looked 30 minutes ago
SELECT * FROM app.orders
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE)
WHERE customer_id = 1042;

-- At an exact SCN
SELECT COUNT(*) FROM app.orders AS OF SCN 48211730;

#Put deleted rows back

INSERT INTO app.orders
SELECT * FROM app.orders AS OF TIMESTAMP TIMESTAMP '2026-10-31 09:40:00'
WHERE order_id NOT IN (SELECT order_id FROM app.orders);
COMMIT;

#Undo a bad UPDATE on one column

MERGE INTO app.orders o
USING (SELECT order_id, status
       FROM app.orders AS OF SCN 48211730) old
ON (o.order_id = old.order_id)
WHEN MATCHED THEN UPDATE SET o.status = old.status
WHERE o.status <> old.status;
COMMIT;

Tip

For anything bigger than a handful of rows, copy the old image into a staging table first with CREATE TABLE app.orders_0940 AS SELECT * FROM app.orders AS OF SCN 48211730;. The undo you depend on can be overwritten while you are still writing the fix.

Privilege needed: FLASHBACK on the table (or FLASHBACK ANY TABLE) plus SELECT.

#Flashback Version Query: who changed what

SELECT versions_startscn, versions_starttime, versions_endtime,
       versions_xid, versions_operation, order_id, status
FROM app.orders
VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '2' HOUR AND SYSTIMESTAMP
WHERE order_id = 88812
ORDER BY versions_startscn;

versions_operation is I, U or D. Take the versions_xid to Flashback Transaction Query:

SELECT operation, table_name, logon_user, start_timestamp, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('0A001F00B2340000');

undo_sql is only populated when minimal supplemental logging was on at the time of the change (ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;). Querying the view needs SELECT ANY TRANSACTION.

#Flashback Table: rewind a whole table

ALTER TABLE app.orders ENABLE ROW MOVEMENT;

FLASHBACK TABLE app.orders TO TIMESTAMP TIMESTAMP '2026-10-31 09:40:00';
-- or
FLASHBACK TABLE app.orders TO SCN 48211730;
-- or several related tables together, keeping constraints consistent
FLASHBACK TABLE app.orders, app.order_lines TO SCN 48211730;

Warning

Flashback Table rewinds every row in the table, including good changes made after the target time. It runs as one transaction and holds exclusive DML locks on the tables while it runs. Agree a short write freeze with the application team first.

It fails if the table's structure changed since the target time (columns added or dropped, table truncated, ORA-01466), or if row movement is off (ORA-08189). Indexes are maintained; statistics are not reverted.

#Flashback Drop: get a dropped table back

SHOW RECYCLEBIN

SELECT object_name, original_name, type, droptime, can_undrop
FROM dba_recyclebin
WHERE owner = 'APP' ORDER BY droptime DESC;

FLASHBACK TABLE app.orders TO BEFORE DROP;
-- if a new table with the same name now exists:
FLASHBACK TABLE app.orders TO BEFORE DROP RENAME TO orders_restored;

Dependent indexes and triggers come back with their BIN$... names. Rename them:

SELECT index_name FROM dba_indexes WHERE owner = 'APP' AND table_name = 'ORDERS';
ALTER INDEX app."BIN$x1Yz...==$0" RENAME TO orders_pk;

Limits to know:

• DROP TABLE ... PURGE, PURGE RECYCLEBIN and space pressure in the tablespace remove objects permanently.
• Tables in the SYSTEM tablespace do not go to the recycle bin.
• Foreign key constraints referencing other tables are not restored; recreate them.
• TRUNCATE does not use the recycle bin. You need Flashback Database or a restore.
• Check the setting: SHOW PARAMETER recyclebin (should be ON).

#Flashback Database: rewind everything

#Turn it on and check how far back you can go

SHOW PARAMETER db_recovery_file_dest
ALTER SYSTEM SET db_flashback_retention_target = 1440 SCOPE = BOTH;  -- minutes
ALTER DATABASE FLASHBACK ON;

SELECT flashback_on, log_mode FROM v$database;
SELECT oldest_flashback_scn, oldest_flashback_time, retention_target,
       flashback_size/1024/1024/1024 AS flashback_gb
FROM v$flashback_database_log;

It requires ARCHIVELOG mode and a fast recovery area. The retention target is a goal, not a promise: if the FRA fills, Oracle deletes old flashback logs.

#Guaranteed restore point before a release or upgrade

CREATE RESTORE POINT before_rel_42 GUARANTEE FLASHBACK DATABASE;

SELECT name, scn, time, guarantee_flashback_database, storage_size/1024/1024 AS mb
FROM v$restore_point;

Caution

A guaranteed restore point keeps flashback logs and archive logs until you drop it. Forgotten restore points are a common cause of a full FRA and an ORA-00257 archiver hang. Drop it as soon as the release is signed off: DROP RESTORE POINT before_rel_42;

#Rewind the database

SHUTDOWN IMMEDIATE
STARTUP MOUNT
FLASHBACK DATABASE TO RESTORE POINT before_rel_42;
-- or TO SCN 48211730 / TO TIMESTAMP TIMESTAMP '2026-10-31 09:40:00'

ALTER DATABASE OPEN READ ONLY;   -- check the data first
-- if correct:
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;

Opening with RESETLOGS starts a new incarnation; take a fresh RMAN backup afterwards. If the target is older than the available logs you get ORA-38729, and only an RMAN restore can go further back. On a Data Guard primary, flashing back affects the standby; see the broker reinstate steps in your Data Guard runbook.

#Rewind one PDB only

ALTER PLUGGABLE DATABASE sales_pdb CLOSE IMMEDIATE INSTANCES = ALL;
FLASHBACK PLUGGABLE DATABASE sales_pdb TO RESTORE POINT before_rel_42;
ALTER PLUGGABLE DATABASE sales_pdb OPEN RESETLOGS;

Create a PDB-level restore point from inside the PDB or with CREATE RESTORE POINT ... FOR PLUGGABLE DATABASE sales_pdb. PDB flashback is simplest with local undo (SELECT property_value FROM database_properties WHERE property_name = 'LOCAL_UNDO_ENABLED';).

#Settings that decide how far back you can go

SHOW PARAMETER undo_retention
SELECT MAX(tuned_undoretention) AS tuned_seconds FROM v$undostat;
SELECT tablespace_name, retention FROM dba_tablespaces WHERE contents = 'UNDO';

If a Flashback Query fails with ORA-01555, the undo for that time is gone. Move to Flashback Database (if logging covers it) or an RMAN point-in-time restore of a copy.

#Prevent it next time

• Create a guaranteed restore point before every schema release and upgrade, and drop it at sign-off.
• Size undo for at least the window you want to recover from (for example 3-6 hours) and check v$undostat monthly.
• Enable Flashback Database on production with a retention target your FRA can actually hold, and alert on FRA usage.
• Turn on minimal supplemental logging if you want undo_sql for investigations.
• For long-term row history (audit, months or years), look at Flashback Data Archive (called Flashback Time Travel in 23ai) instead of stretching undo.