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.
