Flashback Data Archive in Oracle 11g

Sample of Flashback Data Archive (FDA) in Oracle 11g
CREATE TABLESPACE fda_ts DATAFILE '+DG1/fda1_01.dbf' SIZE 1M AUTOEXTEND ON;

CREATE FLASHBACK ARCHIVE DEFAULT fda_1_year TABLESPACE fda_ts QUOTA 1G RETENTION 1 YEAR; CREATE USER fda_test_user IDENTIFIED BY fda_test_user QUOTA UNLIMITED ON users;

GRANT CONNECT, CREATE TABLE TO fda_user IDENTIFIED BY fda_user; GRANT FLASHBACK ARCHIVE ON fda_1_year TO fda_user;

CONNECT fda_user/fda_user

SQL> CREATE TABLE fda_table (a NUMBER, b VARCHAR2(10)) FLASHBACK ARCHIVE fda_1_year;

SQL> INSERT INTO fda_table values (1, 'A'); SQL> INSERT INTO fda_table values (2, 'A'); SQL> INSERT INTO fda_table values (3, 'C'); SQL> COMMIT;

SQL> SELECT * FROM fda_table;

A B -- -- 1 A	 2 A	 3 C

SQL> CREATE VIEW fda_view AS SELECT * FROM fda_table WHERE B = 'A';

SELECT * FROM fda_view; SQL> SELECT * FROM fda_view;

A B -- -- 1 A	 2 A

SQL> UPDATE fda_table SET b = 'B' WHERE a = 2; SQL> COMMIT;

SQL> SELECT * FROM fda_table;

A B -- -- 1 A	 2 B	 3 C

SQL> SELECT * FROM fda_view;

A B -- -- 1 A

SQL> SELECT * FROM fda_table AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '3' MINUTE;

A B -- -- 1 A	 2 A	 3 C

SQL> SELECT * FROM fda_view AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '3' MINUTE;

A B -- -- 1 A	 2 A

References:
 * http://www.oracle-base.com/articles/11g/flashback-and-logminer-enhancements-11gr1.php#flashback_data_archive