Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

Introduction
With Oracle 11g, Oracle have improved the Flashback technologies and now it’s a solid part of the recovery solutions Oracle provides.
Oracle flashback data archives provides the ability to track changes made on a table over its lifetime.
Now, You can stop using your logging scripts built on previous versions of Oracle, and start using the flashback data archives.The flashback data archives can also be used for data analysis and within a decision support system (DSS) environment.
In this article, we are going to setup the Oracle flashback data archives and then how it can be used.

Before you start
Any user concerned by the flashback data archives must have the flashback archive administer privilege granted.
Grant FLASHBACK ARCHIVE ADMINISTER to WISSEM;

Example
In this example, we create the archive with a retention period of one day (in real world, this retention period may be in term of months, even years).
CAUTION Archives can get quite large. make sure you allocate enough space
1- We first create a flashback data tablespace.
2- We create a test table.
3- We enable the archive data on the test table.

Query the test table using the AS OF clause, specifying a timestamp prior to the delete timestamp.
Then, just to verify that we are using the flashback data archive and not the content of the undo tablespace, we create a new tablespace, make it the default, switch it it the default tablespace and drop the old undo tablespace.

CAUTION If you turn off the data archives using alter table fb_data_archive no flashback archive, you will lose all the historical data for that table. The same happens if you drop or purge all the flash back data archives.
Drop the flash back data archives:
Drop flashback archive default_flash_archive;
Purge all historical data from flash back data archive:
Alter flashback archive default_flash_archive PURGE ALL;

Note that when a table is in data archives mode, certain DDL statements may fail, especially the drop command;

<br />
SQL&amp;gt; alter table fb_data_archive add col number;<br />
Table altered.<br />
SQL&amp;gt; drop table fb_data_archive;<br />
drop table fb_data_archive<br />
*<br />
ERROR at line 1:<br />
&lt;strong&gt;ORA-55610: Invalid DDL statement on history-tracked table&lt;/strong&gt;<br />
SQL&amp;gt; truncate table fb_data_archive;<br />
Table truncated.<br />
SQL&amp;gt; alter table fb_data_archive rename to fb_data_archive_2;<br />
Table altered.<br />
SQL&amp;gt;<br />

To avoid this error, when dropping the table, you must first disable the flashback data archives on this table.

<br />
SQL&amp;gt; alter table fb_data_archive rename to fb_data_archive_2;<br />
Table altered.<br />
SQL&amp;gt; alter table fb_data_archive_2 no flashback archive;<br />
Table altered.<br />
SQL&amp;gt; drop table fb_data_archive_2;<br />
Table dropped.<br />

The Oracle Flashback data archive views
To administer the flashback data archives, you can use one of following views;
- DBA_FLASHBACK_ARCHIVE: All the flashback data archives information.
- USER_FLASHBACK_ARCHIVE: Your flashback data archives information.
- DBA_FLASHBACK_ARCHIVE_TS: All tablespaces related to the flashback data archives.
- DBA_FLASHBACK_ARCHIVE_TABLES: All tables with data archives enabled.
- USER_FLASHBACK_ARCHIVE_TABLES: Your tables with data archives enabled.

Nice reading,