Flashback Table
Oracle Requirements:
FLASHBACK TABLE privilege.
Sufficient UNDO tablespace.
ROW MOVEMENT must be enabled on the table.
Licensing:
Requires Oracle Database Enterprise Edition.
Flashback Table (In Time) in Oracle
The Flashback Table feature in Oracle allows you to revert one or more tables to a previous state without the need for traditional point-in-time recovery. This can be done by specifying a point in time or an SCN (System Change Number).
This feature is especially useful for undoing accidental UPDATE, DELETE, or INSERT operations, providing a fast and efficient way to recover data.
How Does Flashback Table Work?
Flashback Table uses undo data to rewind tables to a specific point in time. The process preserves the table structure, indexes, and triggers while restoring the data to its previous state.
Key Characteristics of Flashback Table:
- Efficient Recovery – Allows table-level recovery without affecting the entire database.
- Minimized Downtime – Data can be recovered in seconds without requiring complex restore procedures.
- Selective Recovery – Only the affected table(s) are restored, leaving the rest of the database untouched.
Configure Undo Properly
Flashback Query relies on undo data, so ensuring that you have sufficient undo tablespace is critical.
Verify Undo Tablespace Configuration
SQL> SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb, guarantee FROM dba_data_files WHERE tablespace_name LIKE 'UNDOTBS%';
Check and extend the Undo Retention Period - In seconds
SQL> SHO PARAMETER UNDO_RETENTION;
SQL> ALTER DATABASE SET UNDO_RETENTION = 900;
I also recommend to configure the UNDO tablespace to be guarantee, to avoid data from being purged due to lack of free space
SQL> SHO PARAMETER undo_tablespace
SQL> ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;
Enabling Row Movement
For Flashback Table to work, row movement must be enabled on the target table.
Enable Row Movement
SQL> ALTER TABLE employees ENABLE ROW MOVEMENT;
Syntax and Examples
Flashback Table to a Specific Time
SQL> FLASHBACK TABLE MY_TABLE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '30' MINUTE);
SQL> FLASHBACK TABLE MY_TABLE TO
TO_TIMESTAMP('2024-12-20 09:30:00', 'YYYY-MM-DD HH:MI:SS');
If you want to use the SCN instead
SQL> FLASHBACK TABLE MY_TABLE TO SCN 123456;
Viewing the Current SCN
To determine the current SCN, use the following query:
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
Limitations and Considerations
- Undo Retention – The ability to flashback depends on the undo retention period.
- Foreign Keys – Tables with foreign key constraints may have restrictions during flashback operations.
- Disabled Triggers – Triggers on the table are disabled during the flashback process. Use ENABLE TRIGGERS if needed.
- Take a Table Export – I always recommend to take a table export as a backup before the flashback process, users don't always know what they are asking for.