Oracle Flashback

Flashback Data Archive (FBDA)

Flashback Database

Check

SELECT flashback_on

FROM v$database;

Enable

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=2880 SCOPE=BOTH;

2880 Minutes is 2 Days

ALTER DATABASE FLASHBACK ON;

ARCHIVELOG Mode is a pre-requisite for Flashback Database.
FRA must be appropriately sized for the expected number of Flashback Logs

Disable

ALTER DATABASE FLASHBACK OFF;

Flashback Table

To "undrop" a table...

FLASHBACK TABLE '%TABLE' TO BEFORE DROP;

To "undelete" rows...

ALTER TABLE '%TABLE' ENABLE ROW MOVEMENT;

FLASHBACK TABLE '%TABLE' TO TIMESTAMP TO_TIMESTAMP('2021-09-22 14:46:01','YYYY-MM-DD HH24:MI:SS');

NOTE: Be careful. This will shift the table back in time. Any new rows INSERTED/UPDATED after the initial DELETE will be lost.

PERFORMANCE
On a sample table with 6852297 rows, deleting 6092207 rows with a single DELETE transaction leaving 760090 rows...
DELETE took just over 11 minutes. FLASHBACK took just under 17 minutes

Flashback Query

To query contents of a table at an earlier point in time...

SELECT *

FROM mytable AS OF TIMESTAMP TO_TIMESTAMP('2021-07-16 22:36:00','YYYY-MM-DD HH:MI:SS')

WHERE mycolumn = 'myvalue';

You can also use something like the query below to generate SQL to undo the SQL you just did...

SELECT undo_sql

FROM FLASHBACK_TRANSACTION_QUERY

WHERE TABLE_NAME = 'mytable';