Have you ever needed to “verify specific data from several years ago” or “track deleted records” in Oracle Database? Typically, referring to past data utilizes the UNDO tablespace, but this is limited by a retention threshold.
By leveraging Oracle Flashback Data Archive (FDA), you can automatically preserve change histories for months or even years and reconstruct the past at will using a single SQL statement. This article explains everything from the mechanics of FDA to construction and retrieval procedures in a way that is easy for beginners to understand.
Conclusion: Shortest Procedure for FDA Operations (Checklist)
The steps to implement FDA and begin history management are as follows:
- Create a Dedicated Tablespace: Secure physical space to store historical data.
- Create a Flashback Archive (Retention Definition): Determine the retention period (RETENTION).
- Enable FDA on Target Tables: Link the feature using the
ALTER TABLEstatement. - Query Past Data: Search using the
AS OForVERSIONS BETWEENclauses.
Background and Fundamentals: FDA Mechanism and Benefits
Conventionally, “Flashback Query,” which refers to past data, used data from a temporary location called the UNDO tablespace. However, since UNDO data is overwritten by new transactions, its limit for viewing past data is usually several hours to a few days.
In contrast, FDA (also known as Total Recall) permanently records change logs into dedicated history tables.
Comparison of Features
| Feature Name | Primary Use | Data Storage Location | Typical Retention Period |
| Flashback Query | Short-term recovery from accidental operations | UNDO tablespace | Minutes to hours |
| Flashback Data Archive | Long-term auditing and history tracking | Dedicated tablespace | Months to years |
Operational Concept
Change histories are automatically archived by a background process (fbda).
Operating System
┌────────────────────────────────────────────────────────────┐
│ User (Executing DML) │
└──────────────┬─────────────────────────────────────────────┘
│ UPDATE / DELETE
▼
┌────────────────────────────────────────────────────────────┐ Automatic ┌────────────────────────────────────────────────────────────┐
│ Oracle Table (Current Data) │ ── Recording ──▶ │ Flashback Archive │
└────────────────────────────────────────────────────────────┘ └──────────────┬─────────────────────────────────────────────┘
│ Reference via SELECT ... VERSIONS
▼
Reconstruct Past Data State
Implementation Procedures: Creating and Enabling FDA
The following steps are performed in Oracle Database 19c (non-CDB environment or within a PDB) by a user with DBA privileges.
1. Create a Dedicated Tablespace for History Storage
First, prepare a dedicated area so that historical data does not pressure existing business data.
-- Create a 1GB dedicated tablespace (change the path according to your environment)
CREATE TABLESPACE fda_tbs
DATAFILE '/u01/app/oracle/oradata/V19/fda_tbs01.dbf'
SIZE 1G;
-- Confirm creation
SELECT file_name FROM dba_data_files WHERE tablespace_name = 'FDA_TBS';
2. Create a Flashback Archive (Retention Rule)
Define “which tablespace” to use and “how long” to preserve the data.
-- Create an archive definition that retains history for 1 year
CREATE FLASHBACK ARCHIVE fda_archive
TABLESPACE fda_tbs
RETENTION 1 YEAR;
3. Apply to Target Tables
Enable FDA for the tables you wish to manage.
-- Create a verification table
CREATE TABLE sales (
id NUMBER,
amount NUMBER,
updated_at DATE
);
-- Insert initial data
INSERT INTO sales VALUES (1, 1000, SYSDATE);
INSERT INTO sales VALUES (2, 2000, SYSDATE);
COMMIT;
-- Enable FDA (This starts automatic tracking)
ALTER TABLE sales FLASHBACK ARCHIVE fda_archive;
Execution Example:
SQL> CREATE TABLESPACE fda_tbs DATAFILE '/u01/app/oracle/oradata/V19/fda_tbs01.dbf' SIZE 1G; ★Create Tablespace
Tablespace created.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/V19/users01.dbf
/u01/app/oracle/oradata/V19/undotbs01.dbf
/u01/app/oracle/oradata/V19/system01.dbf
/u01/app/oracle/oradata/V19/sysaux01.dbf
/u01/app/oracle/oradata/V19/fda_tbs01.dbf ★
SQL> CREATE FLASHBACK ARCHIVE fda_archive ★Create Archive
2 TABLESPACE fda_tbs
3 RETENTION 1 YEAR;
Flashback archive created.
SQL> CREATE TABLE sales (
2 id NUMBER,
3 amount NUMBER,
4 updated_at DATE
5 );
Table created.
SQL> INSERT INTO sales VALUES (1, 1000, SYSDATE);
1 row created.
SQL> INSERT INTO sales VALUES (2, 2000, SYSDATE);
1 row created.
SQL> commit;
Commit complete.
SQL> ALTER TABLE sales FLASHBACK ARCHIVE fda_archive; ★Enable FDA
Table altered.
Execution Example: Querying Past Data
For tables where FDA is enabled, you can query the past state using the following SQL.
Referencing a Specific Point in Time (AS OF Clause)
To see data as of “May 1, 2026, 10:00 AM”:
SELECT * FROM sales
AS OF TIMESTAMP TO_TIMESTAMP('2026-05-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS');
Checking the Evolution of Change History (VERSIONS Clause)
Display a list of when and which records changed.
-- Track history using pseudocolumns like VERSIONS_STARTTIME
SELECT id, amount, updated_at, VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_OPERATION
FROM sales VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2026-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND SYSTIMESTAMP
ORDER BY VERSIONS_STARTTIME;
- VERSIONS_OPERATION: You can identify I (Insert), U (Update), or D (Delete).
Troubleshooting: Common Errors and Solutions
| ORA Error | Cause | Confirmation/Solution |
| ORA-55610 | DDL restriction on FDA-enabled table | From 19c onwards, many DDLs are allowed, but some restrictions remain. You may need to disable it temporarily with NO FLASHBACK ARCHIVE. |
| ORA-55641 | Insufficient privileges during archive creation | Verify if the FLASHBACK ARCHIVE ADMINISTER privilege has been granted. |
| ORA-01653 | FDA tablespace exhaustion | The history data has filled the space. Extend it using ALTER DATABASE DATAFILE ... RESIZE. |
Operational and Security Considerations
- Monitor Disk Capacity: If FDA is applied to tables with high update frequency, historical data will increase rapidly. Monitoring free space in the FDA tablespace is essential.
- Performance Impact: Since history writing is performed asynchronously (by the fbda process), the direct impact on business transactions is small. However, consider the overhead in environments with massive DML volumes.
- How to Revert (Disable Feature):SQL
ALTER TABLE sales NO FLASHBACK ARCHIVE;
FAQ
Q1: Can I add or delete columns after enabling FDA?
A1: Since Oracle 12c R1, adding (ADD) or renaming columns while FDA is enabled is supported. However, as there are restrictions in older versions, please check the official documentation for OS/DB version compatibility.
Q2: When is historical data deleted?
A2: It is automatically purged (deleted) once the period specified in RETENTION has passed.
Q3: Can it be used in Standard Edition 2 (SE2)?
A3: Yes, since Oracle 11g R2, FDA (basic functionality) is available in Standard Edition.
Summary
- FDA is a feature for preserving data history over long periods.
- It requires a dedicated tablespace but is not subject to the limits of the UNDO tablespace.
- By using the
VERSIONS BETWEENclause, even deleted records can be tracked. - In 19c, management has become easier, making it ideal for auditing and compliance.
This article focuses on Oracle Database 19c (screens and default values may differ for other versions).
[reference]
Using Oracle Flashback Technology
コメント