Flashback Data Archive (Oracle FDA)

What is FDA (Flashback Data Archive) ?

A Flashback Data Archive provides the ability to track and store all transactional changes to a table over its lifetime.A Flashback Data Archive is useful for compliance with record stage policies and audit reports.

A Flashback Data Archive consists of one or more tablespaces. You can have multiple Flashback Data Archives. If you are logged on as SYSDBA, you can specify a default Flashback Data Archive for the system. A Flashback Data Archive is configured with retention time. Data archived in the Flashback Data Archive is retained for the retention time.

Note: An insert transaction will not cause any records to be added to the history table, since that new row does not have a prior history.

1. Create the flashback data archive:

--Create the tablespace that will host the flashback data archive CREATE TABLESPACE FDA_DATA DATAFILE 'D:\ORADATA\TESTDB1\FDA_DATA.DBF' size 50M autoextend on NEXT 1M MAXSIZE UNLIMITED    EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO ; --Create flashback data archive and retain data up to four years before the database automatically purges it. create flashback archive FDA_ARCHIVE tablespace FDA_DATA   quota 2000m  --quota clause is optional retention 4 year;

2. Grant Flashback Archive privilege on Flashback data archive to user Boris:

grant flashback archive on FDA_ARCHIVE to Boris;

3. Enable history tracking for a table in the FDA_ARCHIVE archive:

--Existing table alter table EMP flashback archive FDA_ARCHIVE ; --New table create table EMP2 ( name varchar2(30), address varchar2(50)) flashback archive FDA_ARCHIVE;

4. View the historical data:

--Time and date SQL> select systimestamp from dual; SYSTIMESTAMP ----------------------------------- 22-AUG-14 08.40.56.429000 PM -04:00 --Original value SQL> select ename, sal from emp where empno = 7788; ENAME             SAL ---------- ---------- SCOTT            3000 --Increase salary by 5000 SQL>  update emp set sal=sal+5000 where empno=7788; 1 row updated. SQL> commit; Commit complete. --Updated value SQL> select ename, sal from emp where empno = 7788; ENAME             SAL ---------- ---------- SCOTT            8000 --Historical value SQL> select ename, sal from emp as of     timestamp to_timestamp('2014-08-22 08:40:00','yyyy-mm-dd hh24:mi:ss')     where empno=7788; ENAME             SAL ---------- ---------- SCOTT            3000

5. Monitoring Flashback Data Archives:

--View which tables have flashback data archiving enabled SET linesize 250 COL OWNER_NAME FORMAT A10 COL TABLE_NAME FORMAT A25 COL FLASHBACK_ARCHIVE_NAME FORMAT A25 COL ARCHIVE_TABLE_NAME FORMAT A25 COL STATUS FORMAT A10 select OWNER_NAME, TABLE_NAME, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME, STATUS from dba_flashback_archive_tables; --View all the flashback data archives in the database: SET linesize 250 COL FLASHBACK_ARCHIVE_NAME FORMAT A25 select flashback_archive_name, retention_in_days from dba_flashback_archive; --Description of tablespaces in the flashback archives available in the system SET linesize 250 COL FLASHBACK_ARCHIVE_NAME FORMAT A25 COL TABLESPACE_NAME FORMAT A25 COL ARCHIVE_TABLE_NAME FORMAT A25 COL QUOTA_IN_MB FORMAT A10 select FLASHBACK_ARCHIVE_NAME, FLASHBACK_ARCHIVE#, TABLESPACE_NAME, QUOTA_IN_MB from DBA_FLASHBACK_ARCHIVE_TS;

Turn off the flashback logging to the flashback data archive for a specific table:

alter table scott.emp no flashback archive;

Drop a flashback data archive:

drop flashback archive FDA_ARCHIVE;

Altering a Flashback Data Archive:

--Makes FDA_ARCHIVE the default archive alter flashback archive FDA_ARCHIVE set default; --Adds space to the flashback archive alter flashback archive FDA_ARCHIVE add tablespace users; --Remove tablespace from the flashback archive alter flashback archive FDA_ARCHIVE remove tablespace users; --Changes the quota for the archive alter flashback archive FDA_ARCHIVE modify tablespace FDA_DATA quota 10G; -Changes the archive retention time alter flashback archive FDA_ARCHIVE modify retention 2 year; --Purges all archived data alter flashback archive FDA_ARCHIVE purge all; --Purges data older than 2 days alter flashback archive FDA_ARCHIVE purge before timestamp (systimestamp – interval '2' day);

Allow access to specific objects during queries, grant FLASHBACK privilege:

grant FLASHBACK on hr.employee to DONNA;

For more information see:

Oracle® Database Advanced Application Developer's Guide

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.