ORACLE - FDA : Flashback Data Archive

FDA holds historical data. It is "ORACLE TOTAL RECALL" option.

Requirements:
1)UNDO management must be automatic.
2)Tablespace for FDA must be ASSM (Automatic Segment Space Management).
3) Enable the FDA for a table (Oracle will create a internal history table for that table)
4) FBDA - new background process will determined intervals (default 5 min) and copied the UNDO data into history tables. Thus UNDO data marked for archiving are not re-used by the database until it stored by the FBDA into history table.

Setup the FDA:
1) Grant FDA role to user:
select * from dba_sys_privs where privilege like '%FLASHBACK ARC%';
grant flashback archive on j_table to  jay;

2) Create FDA table-space:
create FLASHBACK ARCHIVE  [DEFAULT] j_tbs
Tablespace fda_archive  [mandatory - must be ASSM]
Quota 5G [optional]
RETENTION 24 monthl [mandatory - year, month, day]

3)Enable and alter FDA on table level:
create table j_table (col number) FLASHBACK ARCHIVE;
alter table j_table FLASHBACK ARCHIVE; [deault archiving ]
alter table j_table FLASHBACK ARCHIVE j_tbs; [ non-deault archiving ]
alter table j_table NO FLASHBACK ARCHIVE; [disable archiving]

alter FLASHBACK ARCHIVE jflash SET DEFAULT;
alter FLASHBACK ARCHIVE jflash ADD[REMOVE] TABLESPACE j_tbs2;[Add/remove tablespace]
alter FLSHBACK ARCHIVE jflash MODIFY TABLESPACE QUOTA 8G;
alter FLSHBACK ARCHIVE jflash MODIFY TABLESPACE j_tbs; [Unlimited tablespace]
alter FLSHBACK ARCHIVE jflash MODIFY RETENTION 2 YEAR;
alter FLSHBACK ARCHIVE jflash PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '3' DAY);
alter FLSHBACK ARCHIVE jflash  PURGE ALL:

4) Query the FDA:
select salary from jay.j_table AS OF TIMESTAMP TO_TIMESTAMP ()
select salary from jay.j_table AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '3' MONTH);
FALSHBACK TABLE j_table to TIMESTAMP (SYSTIMESTAMP - INTERVAL '3' MONTH);

5) Query thd FDA dba tables:
select * from DBA_FLASHBACK_ARCHIVE;
select * from DBA_FLASHBACK_ARCHIVE_TS;
select table_name, owner_name, flashback_archive_name from DBA_FLASHBACK_ARCHIVE_TABLES;

6) Most of the Limitation for FDA table has been removed in 11gR2:
Oracle Database 11g Release 2 (11.2) users can now use most DDL commands on tables that are being tracked with Flashback Data Archive. This includes:

• Add, Drop, Rename, Modify Column
• Drop, Truncate Partition
• Rename, Truncate Table
• Add, Drop, Rename, Modify Constraint