Flashback archive (Oracle 11g)

In Oracle 11g, you have the possibility to use flashback archive. This feature allow you to keep track of data change on specific table (used by your application for example).

Here, we will use this feature to keep track of historical data on transaction on specific table.

Fisrt, we need to create a tablespace (TBS_FBARC) that will store these data:

CREATE TABLESPACE TBS_FBARC DATAFILE 
  '/u01/datafile/tbs_fbarc_01.dbf' SIZE 10m AUTOEXTEND ON NEXT 8K MAXSIZE 5120M,
  '/u01/datafile/tbs_fbarc_02.dbf' SIZE 10M AUTOEXTEND ON NEXT 8K MAXSIZE 5120M
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Then, we create the flashback archive, specify what tablespace to use, the retention time to keep historical data and grant rights on the application user (APPUSR) to use it.

CREATE FLASHBACK ARCHIVE DEFAULT FBARC
TABLESPACE TBS_FBARC
RETENTION 3 DAY;

alter flashback archive FBARC set default;
grant flashback archive on FBARC to APPUSR;

We can now add tables:

alter table APPUSR.TABLE1 flashback archive FBARC;
alter table APPUSR.TABLE2 flashback archive FBARC;

If you want (or need) to remove this feature for a table:

alter table APPUSR.TABLE2 NO flashback archive;

To check what table(s) are in the flashback archive, you need to query the table DBA_FLASHBACK_ARCHIVE_TABLES.

Attention, the UNDO tablespace has to be monitored closely, if the flashback archive tablespace is too small or the retention too big, it will be filled really quickly!!

You can monitor the space used by each tables using flashback archive with this query:

 SELECT T.Owner_name,
         T.Table_name,
         S.Tablespace_name,
         SUM (S.Bytes)/1024/1024 Total_space
    FROM Sys.Dba_flashback_archive_tables T, Sys.Dba_segments S
   WHERE     T.Owner_name = S.Owner
         AND T.Flashback_archive_name = 'FBARC'
         AND S.Segment_name LIKE 'SYS_FBA%' || SUBSTR (Archive_table_name,
                           INSTR (Archive_table_name, '_', -1, 1) + 1,
                           LENGTH (Archive_table_name))
GROUP BY T.Owner_name, T.Table_name, S.Tablespace_name
ORDER BY 4 desc;

Supplemental documentation can be found here:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm

I will resume this at a later stage and add the most important information on this post.