ARCHIVE statement to create a flashback data archive, which provides the ability to automatically track and archive transactional data changes to specified database objects. A flashback data archive consists of multiple tablespaces and stores historic data from all transactions against tracked tables. The data is stored in internal history tables.
Flashback data archives retain historical data for the time duration specified using the
RETENTION parameter. Historical data can be queried using the Flashback Query
OF clause. Archived historic data that has aged beyond the specified retention period is automatically purged.
Flashback data archives retain historical data across data definition language (DDL) changes to tables enabled for flashback data archive. Flashback data archives supports a lot of common DDL statements, including some DDL statements that alter table definitions or incur data movement. DDL statements that are not supported result in error ORA-55610.
Oracle Database Advanced Application Developer's Guide for general information on using flashback data archives
TABLE flashback_archive_clause for information on designating a table as a tracked table
ALTER FLASHBACK ARCHIVE for information on changing the quota and retention attributes of the flashback data archive, as well as adding or changing tablespace storage for the flashback data archive
You must have the
ADMINISTER system privilege to create a flashback data archive. In addition, you must have the
TABLESPACE system privilege to create a flashback data archive, as well as sufficient quota on the tablespace in which the historical information will reside. To designate a flashback data archive as the system default flashback data archive, you must be logged in as
You must be logged in as
SYSDBA to specify
DEFAULT. Use this clause to designate this flashback data archive as the default flashback data archive for the database. When a
TABLE statement specifies the
flashback_archive_clause without specifying a flashback data archive name, the database uses the default flashback data archive to store data from that table.
You cannot specify this clause if a default flashback data archive already exists. However, you can replace an existing default flashback data archive using the
TABLEflashback_archive_clause for more information
Specify the name of the flashback data archive. The name must satisfy the requirements specified in "Database Object Naming Rules".
Specify the tablespace where the archived data for this flashback data archive is to be stored. You can specify only one tablespace with this clause. However, you can subsequently add tablespaces to the flashback data archive with an
Specify the amount of space in the initial tablespace to be reserved for the archived data. If the space for archiving in a flashback data archive becomes full, then DML operations on tracked tables that use this flashback data archive will fail. The database issues an out-of-space alert when the content of the flashback data archive is 90% of the specified quota, to allow time to purge old data or add additional quota. If you omit this clause, then the flashback data archive has unlimited quota on the specified tablespace.
DATA to instruct the database to optimize the storage of data in history tables using any of the following features: Advanced Row Compression, Advanced LOB Compression, Advanced LOB Deduplication, segment-level compression tiering, and row-level compression tiering.
DATA to instruct the database not to optimize the storage of data in history tables. This is the default.
DATAclause is available starting with Oracle Database 11g Release 2 (18.104.22.168).
Specify the length of time in months, days, or years that the archived data should be retained in the flashback data archive. If the length of time causes the flashback data archive to become full, then the database responds as described in flashback_archive_quota.
The following statement creates two flashback data archives for testing purposes. The first is designated as the default for the database. For both of them, the space quota is 1 megabyte, and the archive retention is one day.
CREATE FLASHBACK ARCHIVE DEFAULT test_archive1 TABLESPACE example QUOTA 1 M RETENTION 1 DAY; CREATE FLASHBACK ARCHIVE test_archive2 TABLESPACE example QUOTA 1 M RETENTION 1 DAY;
The next statement alters the default flashback data archive to extend the retention period to 1 month:
ALTER FLASHBACK ARCHIVE test_archive1 MODIFY RETENTION 1 MONTH;
The next statement specifies tracking for the
oe.customers table. The flashback data archive is not specified, so data will be archived in the default flashback data archive,
ALTER TABLE oe.customers FLASHBACK ARCHIVE;
The next statement specifies tracking for the
oe.orders table. In this case, data will be archived in the specified flashback data archive,
ALTER TABLE oe.orders FLASHBACK ARCHIVE test_archive2;
The next statement drops
test_archive2 flashback data archive:
DROP FLASHBACK ARCHIVE test_archive2;