Flashback Time Travel

Configuration:

• DATA ARCHIVE

Limitations:

• You must configure the tables in advance. It may consume a lot of space and can cause performance degradation on highly transactional objects.

Operation:

• Row-level querying

Version:

• 11gR1 / 2007

Licensing:

• Does not require Enterprise Edition

Summary

Flashback Time Travel lets you query historical changes at the row level without depending on the UNDO tablespace. Instead, it uses a dedicated Flashback Data Archive (FDA) with a defined retention policy, allowing you to keep past data for a long period and query it later with Flashback features (such as Flashback Query and Flashback Versions Query). Below you will find configuration notes, limitations, operating mode, version and licensing notes, and step-by-step examples.

Flashback Time Travel

It's a feature that allows us to query record changes at the row level of a table. In short, it allows us to use operations like FLASHBACK VERSIONS QUERY and FLASHBACK QUERY without needing to use a tablespace UNDO.

If you are not familiar with the flashback features mentioned above, I recommend reading the linked articles.

In this model, we can create a tablespace and configure a retention ... we can keep old data for querying for many years.

First, we create a tablespace to store the data — instead of UNDO

SQL> CREATE TABLESPACE fda_ts
DATAFILE SIZE 1G 
AUTOEXTEND ON NEXT 100M MAXSIZE 30G;

Tablespace created.

Now we need to grant the tablespace quota to the user/application user.

SQL> ALTER USER guob2025 QUOTA UNLIMITED ON fda_ts;
	
User altered.

Then let`s create the DATA ARCHIVE object with the required retention - which in my case will be one year.

SQL> CREATE FLASHBACK ARCHIVE DEFAULT fda_1year 
TABLESPACE fda_ts QUOTA 10G 
RETENTION 1 YEAR;

Flashback archive created.

Now that we have everything set up, let`s verify the DATA ARCHIVE configurations:

SQL> SELECT owner_name,
       flashback_archive_name,
       flashback_archive#,
       retention_in_days,
       TO_CHAR(create_time, 'DD-MON-YYYY HH24:MI:SS') AS create_time,
       status
FROM   dba_flashback_archive
ORDER BY owner_name, flashback_archive_name;

OWNER_NAME FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RET_DAYS CREATE_TIME         
---------- ---------------------- ------------------ -------- --------------------
SYS        FDA_1YEAR                               1      365 20-AUG-2025 15:21:16

Check the tablespaces used by the DATA ARCHIVE:

SQL> SELECT flashback_archive_name,
       flashback_archive#,
       tablespace_name,
       quota_in_mb
FROM   dba_flashback_archive_ts
ORDER BY flashback_archive_name;

FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME      QUOTA_IN_MB
---------------------- ------------------ -------------------- -----------
FDA_1YEAR                               1 FDA_TS               10240

Enable Flashback on the table ...

SQL> ALTER TABLE guob2025.subscriptions FLASHBACK ARCHIVE fda_1year;
	
Table altered.

Show the tables that are using the DATA ARCHIVE.

SQL> SELECT owner_name,
       table_name,
       flashback_archive_name,
       archive_table_name,
       status
FROM   dba_flashback_archive_tables
ORDER BY owner_name, table_name;

OWNER_NAME TABLE_NAME    FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS 
---------- ------------- ---------------------- ------------------ -------
GUOB2025   SUBSCRIPTIONS FDA_1YEAR              SYS_FBA_HIST_74342 ENABLED

Here`s an example of using the Flashback Versions Query - If the table has the DATA ARCHIVE, it will use it instead of the UNDO segments.

SQL> SELECT
    hextoraw(versions_xid) xid,
    TO_CHAR(versions_starttime, 'YYYY-MM-DD HH24:MI:SS') xid_time,
    CASE
        WHEN versions_operation = 'I' THEN 'INSERT'
        WHEN versions_operation = 'U' THEN 'UPDATE'
        WHEN versions_operation = 'D' THEN 'DELETE'
    END AS operation,
    id,
    name
FROM guob2025.subscriptions
VERSIONS BETWEEN
    TIMESTAMP TO_TIMESTAMP('2025-08-20 16:30:00','YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2025-08-20 16:51:00','YYYY-MM-DD HH24:MI:SS')
WHERE id IN (2504, 2505)
ORDER BY 2;

XID                  XID_TIME             OPERATION      ID NAME
-------------------- -------------------- ---------- ------ --------------------
0100080043030000     2025-08-20 16:34:09  INSERT       2504 Adriano Bonacin
05001A0016030000     2025-08-20 16:35:09  INSERT       2505 Acacio Lima Rocha
07000C0026030000     2025-08-20 16:46:45  UPDATE       2504 Bona
02001B0029030000     2025-08-20 16:48:01  UPDATE       2505 Lima Rocha
020014002A030000     2025-08-20 16:50:22  DELETE       2505 Lima Rocha
020014002A030000     2025-08-20 16:50:22  DELETE       2504 Bona

And here`s another example, now using the Flashback Query - If the table has the DATA ARCHIVE, it will use it instead of the UNDO segments.

SELECT * 
FROM guob2025.subscriptions
AS OF TIMESTAMP TO_TIMESTAMP('2025-08-04 09:30:00', 'YYYY-MM-DD HH:MI:SS');