6 | P a g e F l a s h b a c k D a t a A r c h i v e
(Optional) Whether to optimize the storage of data in the history tables maintained in the
Flashback Data Archive, using [NO] OPTIMIZE DATA.
The default is NO OPTIMIZE DATA.
If you are logged on as SYSDBA, you can also specify that this is the default Flashback Data Archive for
the system. If you omit this option, you can still make this Flashback Data Archive the default later.
Oracle recommends that all users who must use Flashback Data Archive have unlimited quota on the
Flashback Data Archive tablespace; however, if this is not the case, you must grant sufficient quota on
that tablespace to those users. Best practice is to create a separate flashback data archive for each
application module you archive.
Examples:
Create a default Flashback Data Archive named fla1 that uses up to 10 G of tablespace tbs1, whose data
are retained for one year:
CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE tbs1
QUOTA 10G RETENTION 1 YEAR;
Create a Flashback Data Archive named fla2 that uses tablespace tbs2, whose data are retained for two
years:
CREATE FLASHBACK ARCHIVE fla2 TABLESPACE tbs2 RETWNTION 2 YEAR;
FDA Enabling/Disabling
By default, Flashback Data Archive is disabled for any table. You can enable Flashback Data Archive for a
table if you have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive to use for
that table.
To enable Flashback Data Archive for a table, include the FLASHBACK ARCHIVE clause in either the
CREATE TABLE or ALTER TABLE statement. In the FLASHBACK ARCHIVE clause, you can
specify the Flashback Data Archive where the historical data for the table are stored. The default is the
default Flashback Data Archive for the system. If you specify a nonexistent Flashback Data Archive, an
error occurs.
If you enable Flashback Data Archive for a table, but AUM is disabled, error ORA-55614 occurs when
you try to modify the table.
If a table has Flashback Data Archive enabled, and you try to enable it again with a different Flashback
Data Archive, an error occurs.
After Flashback Data Archive is enabled for a table, you can disable it only if you either have the
FLASHBACK ARCHIVE ADMINISTER system privilege or you are logged on as SYSDBA. To disable
Flashback Data Archive for a table, specify NO FLASHBACK ARCHIVE in the ALTER TABLE