Oracle® Database SQL Language Reference 11g Release 2 (11.2) Part Number E17118-04 |
|
|
PDF · Mobi · ePub |
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 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.
See Also:
Oracle Database Advanced Application Developer's Guide for general information on using flashback data archives
The CREATE
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 FLASHBACK
ARCHIVE
ADMINISTER
system privilege to create a flashback data archive. 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. To designate a flashback data archive as the system default flashback data archive, you must be logged in as SYSDBA
.
flashback_archive_retention::=
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 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.
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 ALTER
FLASHBACK
ARCHIVE
statement.
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.
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, 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;