Flashback query reads one table as it was in the past with AS OF. DBMS_FLASHBACK sets the whole session back in time instead: after ENABLE_AT_SYSTEM_CHANGE_NUMBER or ENABLE_AT_TIME, every query sees the database as it was then, until DISABLE. It also returns the current SCN, the database's clock for consistent points in time.
Code for This Guide
The main examples are in the examples/pkg-data-ops folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
v_scn := dbms_flashback.get_system_change_number; dbms_flashback.enable_at_system_change_number(v_scn); dbms_flashback.enable_at_time(timestamp); dbms_flashback.disable;
How far back the session can go depends on the undo data the database keeps.
See the Data Before a Change
Example:
variable scn number exec :scn := dbms_flashback.get_system_change_number update routes set block_minutes = block_minutes + 30 where origin = 'DXB'; commit; select sum(block_minutes) as dxb_minutes_now from routes where origin = 'DXB'; -- from now on, every query of the session sees the database as it was at :scn exec dbms_flashback.enable_at_system_change_number(:scn) select sum(block_minutes) as dxb_minutes_then from routes where origin = 'DXB'; exec dbms_flashback.disable update routes set block_minutes = block_minutes - 30 where origin = 'DXB'; commit;
Output:
PL/SQL procedure successfully completed.
20 rows updated.
Commit complete.
DXB_MINUTES_NOW
__________________
11139
PL/SQL procedure successfully completed.
DXB_MINUTES_THEN
___________________
10539
PL/SQL procedure successfully completed.
20 rows updated.
Commit complete.The SCN is saved, then the block times of the Dubai routes grow by 30 minutes and are committed: 11,139 minutes. With flashback enabled at the saved SCN, the same query returns the old total, 10,539, without any AS OF clause. After DISABLE, the change is undone with a normal update.
Read Only
Example:
exec dbms_flashback.enable_at_time(systimestamp - interval '1' minute) -- in flashback mode the session can only read update routes set block_minutes = block_minutes where route_id = 1; exec dbms_flashback.disable
Output:
PL/SQL procedure successfully completed. Error starting at line : 4 In command - update routes set block_minutes = block_minutes where route_id = 1 Error at Command Line : 4 Column : 8 Error report - SQL Error: ORA-08182: operation not supported while in Flashback mode PL/SQL procedure successfully completed.
While flashback is enabled, the session can only query: DML fails with ORA-08182. Disable flashback before changing data.
Things to Know
- Enable flashback at the start of a transaction: in the middle of one, it fails with ORA-08183.
- Open a cursor while flashback is enabled, disable it, and the cursor still returns past data, which helps copy old rows back.
- Executing DBMS_FLASHBACK needs the EXECUTE privilege on it.
Related Guides
- How to Query Past Data with Flashback Query in Oracle
- How to Restore Dropped Tables from the Recycle Bin in Oracle
Conclusion
DBMS_FLASHBACK puts a whole session back to an SCN or a time, so every query sees past data until you disable it. Use it to run existing reports as of a past moment, and remember that the session cannot change data meanwhile.
