Ittichai at 19:25, 18 May 2012

2012-05-18T19:25:50Z

Ittichai: Created page with "== 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_ye..."

2012-05-18T19:19:41Z

Created page with "== Flashback Data Archive (FDA) in Oracle 11g == <pre> CREATE TABLESPACE fda_ts DATAFILE '+DG1/fda1_01.dbf' SIZE 1M AUTOEXTEND ON; CREATE FLASHBACK ARCHIVE DEFAULT fda_1_ye..."

New page

== Flashback Data Archive (FDA) in Oracle 11g ==

<pre>
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;

</pre>
<pre>
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

</pre>

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

Flashback Data Archive in Oracle 11g - Revision history

Ittichai at 19:25, 18 May 2012

Ittichai: Created page with "== 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_ye..."

@@ Line 1: / Line 1: @@
-== Flashback Data Archive (FDA) in Oracle 11g ==
+== Sample of Flashback Data Archive (FDA) in Oracle 11g ==
 <pre>
@@ Line 30: / Line 30: @@
 	 A B
-----------
+---------- ----------
 A
 A
@@ Line 44: / Line 44: @@
 	 A B
-----------
+---------- ----------
 A
 A
@@ Line 62: / Line 62: @@
 	 A B
-----------
+---------- ----------
 A