Flashback Data Archive in Oracle 11g
Jump to navigation
Jump to search
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: