CREATE FLASHBACK ARCHIVE

Purpose

Use the CREATE FLASHBACK 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.

Flashback data archives retain historical data for the time duration specified using the RETENTION parameter. Historical data can be queried using the Flashback Query AS 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 the database as long as the DDL change does not affect the structure of the table. The one exception to this rule is that flashback data archives do retain historical data when a column is added to the table.

Prerequisites

You must have the FLASHBACK ARCHIVE ADMINISTER system privilege to create a flashback data archive. This privilege can be granted only by a user with DBA privileges. In addition, you must have the CREATE TABLESPACE system privilege to create a flashback data archive, as well as sufficient quota on the tablespace in which the historical information will reside.

Syntax

create_flashback_archive::=

Description of the illustration create_flashback_archive.gif

flashback_archive_quota::=

Description of the illustration flashback_archive_quota.gif

flashback_archive_retention::=

Description of the illustration flashback_archive_retention.gif

Semantics

DEFAULT

Use this clause to designate this flashback data archive as the default flashback data archive for the database. When a CREATE TABLE or ALTER 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 ALTER FLASHBACK ARCHIVE ... SET DEFAULT clause.

flashback_archive

Specify the name of the flashback data archive. The name must satisfy the requirements specified in "Schema Object Naming Rules".

TABLESPACE Clause

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 ALTER FLASHBACK ARCHIVE statement.

flashback_archive_quota

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 the DML operations on tracked tables that use this flashback data archive will fail. The database issues an out-of-space alert for the flashback data archive before the flashback data archive becomes full, 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.

flashback_archive_retention

Specify the length of time in months, days, or years that the archived data should be retained in the flashback data archive.

Examples

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, test_archive1:

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, test_archive2:

ALTER TABLE oe.orders
   FLASHBACK ARCHIVE test_archive2;

The next statement drops test_archive2 flashback data archive:

DROP FLASHBACK ARCHIVE test_archive2;