Oracle 19c Flashback Technology (7/7): Data Archives

In Oracle Database 19c, Flashback Data Archive (FDA) — also known as Total Recall — allows you to track and query historical changes to table data for long-term retention (years if required), independent of UNDO retention.

Unlike normal Flashback Query (which depends on UNDO), Flashback Data Archive stores historical row versions in dedicated archive tables.

FDA is typically used for:

• Regulatory compliance
• Audit tracking
• Data history analysis
• Long-term change tracking

When enabled on a table:

1. Each UPDATE or DELETE creates a historical version.
2. Old row versions are stored in archive tables.
3. You can query past data using AS OF TIMESTAMP.

Oracle manages history automatically.

1. Step-by-Step Configuration

1.1. Create Dedicated Tablespace

CREATE TABLESPACE fda_ts DATAFILE '/u01/app/oracle/oradata/ORCL/pdb/FDA01.dbf'
SIZE 1G AUTOEXTEND ON;

1.2. Create Flashback Archive

CREATE FLASHBACK ARCHIVE fda_archive TABLESPACE fda_ts RETENTION 5 YEAR;

Verify:

SELECT flashback_archive_name, retention_in_days FROM dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME         RETENTION_IN_DAYS
------------------------------ -----------------
FDA_ARCHIVE                                 1825

1.3. Grant Privileges

GRANT FLASHBACK ARCHIVE ON fda_archive TO hr;

Or allow table-level control:

GRANT FLASHBACK ARCHIVE ADMINISTER TO system;

1.4. Enable Flashback Archive on a Table

Example with HR schema:

ALTER TABLE empv4 FLASHBACK ARCHIVE fda_archive;

Verify:

SELECT table_name, flashback_archive_name FROM dba_flashback_archive_tables WHERE table_name = 'EMPV4';
TABLE_NAME                     FLASHBACK_ARCHIVE_NAME
------------------------------ ------------------------------
EMPV4                          FDA_ARCHIVE

2. Practical Example

Step 1 – Check Current Salary

SELECT salary FROM empv4 WHERE employee_id = 100;
    SALARY
----------
  27603.45

Step 2 – Perform Update

UPDATE empv4
SET salary = salary * 1.20
WHERE employee_id = 100;
1 row updated.

COMMIT;

SQL> SELECT salary FROM empv4 WHERE employee_id = 100;

    SALARY
----------
  33124.14

3. Query Historical Data

Using Timestamp

SELECT salary FROM empv4 AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE)
WHERE employee_id = 100;
    SALARY
----------
  27603.45

Using Versions Between

SELECT versions_starttime,
       versions_endtime,
       salary
FROM empv4
VERSIONS BETWEEN TIMESTAMP
     SYSTIMESTAMP - INTERVAL '1' DAY
 AND SYSTIMESTAMP
WHERE employee_id = 100;

Note: Output shows full salary history.

4. Retention Management

Retention example:

ALTER FLASHBACK ARCHIVE fda_archive MODIFY RETENTION 10 YEAR;

Note: Oracle automatically purges data older than retention.

5. Disabling Flashback Data Archive

Disable for a table:

ALTER TABLE empv4 NO FLASHBACK ARCHIVE;

Drop archive:

DROP FLASHBACK ARCHIVE fda_archive;

Dropping deletes historical data permanently.

6. Conclusion

Flashback Data Archive in Oracle 19c provides:

• Long-term historical tracking
• Compliance-ready auditing
• Transparent historical queries