Oracle中的flashback archive

flash data archive 提供了跟踪和保存一个table的事务变化信息的方法. FDA可以包含一个或多个表空间,也可以有多个FDA. 由用户来指定默认的FDA.默认FDA是不跟踪任何表的.
可以通过grant flashback archive administer to user 来给用户赋予修改FDA的权限.

它的功能类似于undo, 工作在逻辑层.但undo有undo_reention, 过了一定的时间还是不能保留,所以flashback archive是对Undo的很好的补充.

When a DML transaction commits an operation on a flashback archive enabled table, the Flashback Data Archiver (FBDA) process stores the pre-image of the rows into a flashback archive, along with metadata of the current rows. The FBDA process is also responsible for managing the data within the flashback archives, such as purging data beyond the retention period.

CREATE TABLESPACE fda_ts DATAFILE '/u01/app/oracle/oradata/DB11G/fda1_01.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE FLASHBACK ARCHIVE DEFAULT fda_1year TABLESPACE fda_ts QUOTA 10G RETENTION 1 YEAR;

CREATE FLASHBACK ARCHIVE fda_2year TABLESPACE fda_ts RETENTION 2 YEAR;--

--Set as default FBA
ALTER FLASHBACK ARCHIVE fba_name SET DEFAULT;-- Add up to 10G of the specified tablespace to the specified flashback archive.
ALTER FLASHBACK ARCHIVE fba_name ADD TABLESPACE ts_name QUOTA 10G;

-- Add an unlimited quota of the specified tablespace to the specified flashback archive. ALTER FLASHBACK ARCHIVE fba_name ADD TABLESPACE ts_name;

-- Change the tablespace quota to 20G.
ALTER FLASHBACK ARCHIVE fba_name MODIFY TABLESPACE ts_name QUOTA 20G;

-- Change the tablespace quota to unlimited.
ALTER FLASHBACK ARCHIVE fba_name MODIFY TABLESPACE ts_name;

-- Remove the specified tablespace from the archive.
ALTER FLASHBACK ARCHIVE fba_name REMOVE TABLESPACE ts_name;

-- Modify the retention
ALTER FLASHBACK ARCHIVE fba_name MODIFY RETENTION 2 YEAR;

-- Remove all historical data.
ALTER FLASHBACK ARCHIVE fba_name PURGE ALL;

-- Remove all data before the specified time.
ALTER FLASHBACK ARCHIVE fba_name PURGE BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY);

-- Remove all data before the specified SCN.
ALTER FLASHBACK ARCHIVE fba_name PURGE BEFORE SCN 728969;

--Archives are removed using the DROP FLASHBACK ARCHIVE command, which drops the archive and all its historical data, but doesn't drop the associated tablespace.

DROP FLASHBACK ARCHIVE fba_name;

--Create user and grant privilege
CONN sys/password AS SYSDBA;
CREATE USER fda_test_user IDENTIFIED BY fda_test_user QUOTA UNLIMITED ON users;
GRANT CONNECT, CREATE TABLE TO fda_test_user;
GRANT FLASHBACK ARCHIVE ON fda_1year TO fda_test_user;

--Use the user
CONN fda_test_user/fda_test_user;
CREATE TABLE test_tab_1 ( id NUMBER, desription VARCHAR2(50), CONSTRAINT test_tab_1_pk PRIMARY KEY (id) ) FLASHBACK ARCHIVE;

The ALTER TABLE command allows existing tables to have flashback archiving switched on or off.

-- Enable using the default FBDA.

ALTER TABLE table_name FLASHBACK ARCHIVE;

-- Enable using specific FBDA.

ALTER TABLE table_name FLASHBACK ARCHIVE fda_name;

-- Disable flashback archiving.

ALTER TABLE table_name NO FLASHBACK ARCHIVE;

As with the CREATE TABLE statement, the FLASHBACK ARCHIVE object privilege must be granted on the flashback archive being used.

Once flashback archiving is enabled the table is protected, so it cannot be switched off unless you have the FLASHBACK ARCHIVE ADMINISTER system privilege, or are logged on as SYSDBA.

flashback archive使用时的限制

In addition, there are certain DDL restrictions associated with having flashback archiving enabled in 11gR1. The following operations result in a ORA-55610 error.

• ALTER TABLE statements that drop, rename or modify columns.
• ALTER TABLE statements that performs partition or subpartition operations.
• ALTER TABLE statements that converts a LONG column to a LOB column.
• ALTER TABLE statements that includes an UPGRADE TABLE clause, with or without an INCLUDING DATA clause.
• DROP TABLE statements.
• RENAME TABLE statements.
• TRUNCATE TABLE statements.

In 11gR2 some of these restrictions have been removed and the following DDL is supported.

• Add, drop, rename or modify column.
• Drop or truncate partitions.
• Rename or truncate tables.
• Add, drop, rename or modify constraints.
• More complex DDL can be performed in conjunction with the DISASSOCIATE_FBA and REASSOCIATE_FBA procedures of the DBMS_FLASHBACK_ARCHIVE package.

与flashback archive相关的view

Information about flashback data archives is displayed using the %_FLASHBACK_ARCHIVE view.

The %_FLASHBACK_ARCHIVE_TS view displays the tablespaces and quotas associated with each flashback archive.

The %_FLASHBACK_ARCHIVE_TABLES view displays tables associated with each flashback archive, along with the name of the table holding the historical information.

select * from dba_flashback_archive;