Flashback Technologies: Query, Table, Drop and Database

Oracle DBA Certification 15 min min read Updated: Nov 16, 2026 Intermediate
Flashback Technologies: Query, Table, Drop and Database
Intermediate Topic 6 of 8

Fix Mistakes Without a Restore

Flashback is a family of features that lets you look at, or return to, the past. It is the fastest way to fix human mistakes.

FeatureWhat it doesUses
Flashback QueryShows data as it was at a past timeUndo
Flashback Version QueryShows every version of a rowUndo
Flashback TableRewinds one tableUndo
Flashback DropBrings back a dropped tableRecycle bin
Flashback DatabaseRewinds the whole databaseFlashback logs

Flashback Query

sql SELECT * FROM shop.customers AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE WHERE city = 'Mumbai';

Rows that were deleted 5 minutes ago are visible here. Put them back with an insert:

sql INSERT INTO shop.customers (full_name, city, email) SELECT full_name, city, email FROM shop.customers AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '10' MINUTE WHERE email NOT IN (SELECT email FROM shop.customers);
Limit: Flashback Query only works as far back as the undo is kept (undo_retention).

Version Query

sql SELECT versions_starttime, versions_operation, price FROM shop.products VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR AND SYSTIMESTAMP WHERE product_name = 'Laptop';

Flashback Table

sql ALTER TABLE shop.products ENABLE ROW MOVEMENT; FLASHBACK TABLE shop.products TO TIMESTAMP SYSTIMESTAMP - INTERVAL '15' MINUTE;

Flashback Drop and the Recycle Bin

When you DROP TABLE, Oracle does not delete it. It renames it and puts it in the recycle bin.

sql DROP TABLE shop.products; SELECT object_name, original_name, droptime FROM dba_recyclebin WHERE owner = 'SHOP'; FLASHBACK TABLE shop.products TO BEFORE DROP;
sql -- really delete DROP TABLE shop.products PURGE; PURGE RECYCLEBIN;
Tip: Use DROP ... PURGE only when you are sure. A purged table cannot be flashed back.

Flashback Database

Rewinds the whole database by applying flashback logs backwards. It is much faster than a restore for large databases.

sql -- one time: turn it on (needs the Fast Recovery Area) ALTER SYSTEM SET db_flashback_retention_target = 1440 SCOPE = BOTH; -- minutes ALTER DATABASE FLASHBACK ON; SELECT flashback_on FROM v$database;
sql SHUTDOWN IMMEDIATE; STARTUP MOUNT; FLASHBACK DATABASE TO TIMESTAMP SYSTIMESTAMP - INTERVAL '30' MINUTE; ALTER DATABASE OPEN RESETLOGS;

Guaranteed Restore Points

Before risky work such as an upgrade or a big data change, mark a point you can always return to:

sql CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE; -- if everything goes wrong SHUTDOWN IMMEDIATE; STARTUP MOUNT; FLASHBACK DATABASE TO RESTORE POINT before_upgrade; ALTER DATABASE OPEN RESETLOGS; -- if everything goes fine, free the space DROP RESTORE POINT before_upgrade;
Space warning: A guaranteed restore point keeps all flashback logs from that moment. Drop it when you are finished, or the recovery area will fill.
Flashback is not a backup: It cannot help if a datafile is physically lost. You still need RMAN backups.

Conclusion

Use flashback query and table for everyday mistakes, the recycle bin for dropped tables and Flashback Database or a restore point for big risky changes.

Quick Summary: AS OF to look, FLASHBACK TABLE to rewind, TO BEFORE DROP to undrop, restore points before risky work.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators