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