How to Query Past Data with Flashback Query in Oracle

Someone ran an UPDATE without a WHERE clause and committed it. Or a report changed overnight and you need to know what the numbers were this morning. Oracle can answer both without a backup: a flashback query reads a table as it was at an earlier time, and a versions query lists every committed change to its rows over a period.

This guide covers AS OF TIMESTAMP and AS OF SCN, repairing data with them, VERSIONS BETWEEN and its pseudocolumns, and the limits that apply.

Code for This Guide

The main examples are in the examples/flashback-sampling 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.

How Flashback Query Works

Oracle keeps the old versions of changed data in undo, to roll back transactions and to give every query a consistent view of the data. A flashback query reads that undo to rebuild the table as it was. It needs no setup and no privilege beyond SELECT on the table, but it can only go back as far as the undo still exists: the UNDO_RETENTION parameter asks for 900 seconds by default, and more is often available.

Syntax:

table as of {timestamp | scn} expr

table versions between {scn | timestamp} {expr | minvalue} and {expr | maxvalue}

An SCN, system change number, is the database's internal clock, increasing with every commit. Timestamps are mapped to SCNs, to within a few seconds; use an SCN when you need precision.

Read a Table as It Was

The example uses a small work table with three routes and a base fare of 100, created first. It waits a few seconds after creating the table, for a reason explained below.

Setup:

create table fares_demo as
select route_id, distance_km, 100 as base_fare from routes where route_id <= 3;
-- wait about ten seconds before the flashback query

The example doubles the fares, commits, and then reads the fares as they were one second earlier.

Example:

select route_id, base_fare from fares_demo;

update fares_demo set base_fare = base_fare * 2;
commit;

select route_id, base_fare from fares_demo;

select route_id, base_fare
from   fares_demo as of timestamp (systimestamp - interval '1' second);

Output:

   ROUTE_ID    BASE_FARE
___________ ____________
          1          100
          2          100
          3          100

3 rows updated.

Commit complete.

   ROUTE_ID    BASE_FARE
___________ ____________
          1          200
          2          200
          3          200

   ROUTE_ID    BASE_FARE
___________ ____________
          1          100
          2          100
          3          100

The committed change is visible to the normal query, while the AS OF query still sees the old values. Drop the table afterward with drop table fares_demo purge.

Repair a Mistake

Because a flashback query is an ordinary query, you can insert or update from it. Here a row deleted by mistake is put back from the table as it was a few seconds earlier.

Example:

delete from fares_fix where route_id = 2;
commit;

-- put the deleted row back from the table as it was a few seconds ago
insert into fares_fix
select * from fares_fix as of timestamp (systimestamp - interval '3' second)
where  route_id = 2;
commit;

select route_id, base_fare from fares_fix order by route_id;

Output:

1 row deleted.

Commit complete.

1 row inserted.

Commit complete.

   ROUTE_ID    BASE_FARE
___________ ____________
          1          100
          2          100
          3          100

The work table fares_fix was created like fares_demo above. For an UPDATE gone wrong, update the rows from the AS OF query instead, matching on the key. To rewind a whole table, FLASHBACK TABLE ... TO TIMESTAMP does it in one statement, provided row movement is enabled on the table.

A Flashback Query Cannot Cross a DDL Change

A flashback query cannot reach back past a change to the table's structure. Querying a table as it was before it was created, or before an ALTER TABLE, fails:

Example:

create table fares_new as select route_id, 100 as base_fare from routes where route_id <= 3;

select route_id, base_fare
from   fares_new as of timestamp (systimestamp - interval '1' minute);

Output:

Table FARES_NEW created.

Error starting at line : 3
In command -
select route_id, base_fare
from   fares_new as of timestamp (systimestamp - interval '1' minute)
Error at Command Line : 4 Column : 8
Error report -
SQL Error: ORA-01466: unable to read data - table definition has changed

That is why the work tables above were created a few seconds before the flashback query.

List Every Version with VERSIONS BETWEEN

VERSIONS BETWEEN returns every committed version of each row in a period, with pseudocolumns that describe each version:

The example records the current SCN in an SQLcl bind variable with DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER, updates a route's block time twice with a commit after each, and lists the versions since then. It changes the ROUTES table, so set route 1's block_minutes back to 400 afterward.

Example:

variable start_scn number
exec :start_scn := dbms_flashback.get_system_change_number

update routes set block_minutes = block_minutes + 5 where route_id = 1;
commit;
exec dbms_session.sleep(2)
update routes set block_minutes = block_minutes + 5 where route_id = 1;
commit;

select versions_operation as op, versions_startscn - :start_scn as start_after,
       versions_endscn - :start_scn as end_after, block_minutes
from   routes versions between scn :start_scn and maxvalue
where  route_id = 1
order  by versions_startscn nulls first;

Output:

PL/SQL procedure successfully completed.

1 row updated.

Commit complete.

PL/SQL procedure successfully completed.

1 row updated.

Commit complete.

OP       START_AFTER    END_AFTER    BLOCK_MINUTES
_____ ______________ ____________ ________________
                                1              400
U                  1            4              405
U                  4                           410

The SCNs are shown relative to the starting SCN. The first row is the version that existed before the updates, valid until the first update committed. The second is the version the first update created, and the third, with no end, is the current version created by the second update. MINVALUE and MAXVALUE stand for as far back as undo allows and now.

Things to Know

• If the undo needed has been overwritten, the query fails with ORA-01555: snapshot too old. For history you must keep for days or years, use Flashback Data Archive instead.
• A flashback query returns committed data only; your own uncommitted changes are not part of any past version.
• You can combine AS OF tables with current tables in one query, for example to compare rows before and after a change.

Related Guides

• How to Use Subqueries in Oracle SQL

Conclusion

AS OF TIMESTAMP and AS OF SCN read a table as it was, within the undo that is still available, which makes them the quickest way to see or repair data changed by mistake. VERSIONS BETWEEN lists every committed version of rows, with the operation, transaction, and SCNs of each. Neither can reach back past a change to the table's definition.