Unlock Historical Table Snapshots with Oracle Flashback Data Archive

Background

Oracle databases generate UNDO information to support transaction rollback, but UNDO data is normally overwritten, leading to the ORA-01555 error. Retaining UNDO for longer periods enables point‑in‑time reconstruction of a table’s state at any historical moment.

Flashback Data Archive Overview

Starting with Oracle Database 11g, the Flashback Data Archive (also called Oracle Total Recall) archives UNDO data, allowing comprehensive historical queries. The background process ora_fbda_ccdb (FBDA) writes UNDO to the archive, similar to how REDO is archived by LGWR and ARCH processes.

Preparation

Because the archive uses its own storage, an ASSM (Automatic Segment Space Management) tablespace must be created first.

Setup Steps

Create an ASSM tablespace for the archive:

create tablespace fbda datafile '/oracle/oradata/tqgzs11g/FBDA.dbf' size 200M segment space management auto;

Create the flashback archive with a retention period (e.g., 1 month):

create flashback archive fda tablespace fbda retention 1 month;

Assign a table to the archive (requires FLASHBACK ARCHIVE ADMINISTER privilege): alter table t flashback archive fda; To stop archiving a table, run: alter table table_name no flashback archive; Capture a System Change Number (SCN) before making changes, then query the table as of that SCN:

select dbms_flashback.get_system_change_number from dual;

select count(*) from t as of scn 1142115;

Testing UNDO Aging

For demonstration, the UNDO tablespace is reduced so UNDO data ages quickly:

create undo tablespace UNDOTBS2_SMALL datafile '/oracle/oradata/tqgzs11g/UNDOTBS2_SMALL.dbf' size 20M autoextend off;

alter system set undo_tablespace=UNDOTBS2_SMALL;

After deleting rows and committing, the SCN‑based query returns the original row count.

PL/SQL Loop to Accelerate UNDO Aging

begin
  for i in 1..100 loop
    delete from t where rownum < 31;
    commit;
  end loop;
end;
/

This loop repeatedly deletes rows, causing UNDO to be overwritten and demonstrating how older flashback data is pruned.

Flashback Query Execution Plans

Comparing the execution plan of a normal query with that of an AS‑OF‑SCN query shows the optimizer accessing the internal flashback archive tables (e.g., SYS_FBA_TCRV_72661) instead of the base table.

Dictionary Views for Archive Information

Various data‑dictionary views expose flashback archive objects:

select * from user_flashback_archive_tables;

select table_name,tablespace_name from user_tables where table_name like '%FBA%';

select table_name from dict where table_name like '%FLASHBACK_ARCHIVE%';

These queries list the archive tables, their tablespaces, and related objects such as SYS_FBA_DDL_COLMAP_72661, SYS_FBA_TCRV_72661, and associated indexes.

Conclusion

Flashback Data Archive is a powerful feature in Oracle 11g that extends data lifecycle management by preserving UNDO history for months, enabling point‑in‑time analysis, auditing, and recovery without relying on traditional backups.