Total Recall

One of the most interesting new features from my point of view in 11g is the new Total Recall option with it's Flashback Data Archive. Now it's possible to track data changes in the past in a very efficient manner both in terms of performance and disk consumption. Here's a quick example of setting up Flashback Data Archive:

CREATE FLASHBACK ARCHIVE DEFAULT FDA_1_YEAR TABLESPACE FDA_1_YEAR
QUOTA 100M RETENTION 1 YEAR;

alter table scott.emp flashback archive fda_1_year;

In the example above we created a flashback data archive (FDA)and then set the emp table to be included in the FDA and we wished to retain the flashback information for one year.

Having done this it's now possible to look backwards in time for the values of emp table using the 'as of' flashback sql-structure. An example of this would be:

select ename, sal from scott.emp as of timestamp to_timestamp('2007-14-08 15:30:00','YYYY-DD-MM HH24:MI:SS');

We can see from dba_flashback_archive_tables that Oracle stores the flashback information in a separate table which is actually range partioned and compressed to maximize performance and minimize space comsumption:

select table_name, archive_table_name from dba_flashback_archive_tables;

TABLENAME ARCHIVE_TABLE_NAME
------------------------------ -----------------------------------------------------
EMP SYS_FBA_HIST_69515

SQL> select compression, compress_for from dba_tab_partitions where table_name='SYS_FBA_HIST_69515';

COMPRESS COMPRESS_FOR
-------- ------------------
ENABLED FOR ALL OPERATIONS <-- This is new in Oracle 11g and means that compression will be continuous for this table.

FDA will be the way to go if you have the need to retain historical data changes for your database.