6.2.2.5 修改配额不受限制
SQL> alter flashback archive fla1 modify tablespace fda1;
Flashback archive altered.
6.2.2.6 修改Flashback Archive 的retention time
SQL> alter flashback archive fla1 modify retention 2 year;
Flashback archive altered.
SQL> alter flashback archive fla1 modify retention 1 month;
Flashback archive altered.
SQL> alter flashback archive fla1 modify retention 2 month;
Flashback archive altered.
SQL> alter flashback archive fla1 modify retention 2 day;
Flashback archive altered.
SQL> alter flashback archive fla1 modify retention 1 day;
Flashback archive altered.
6.2.2.7 将表空间从Flashback Archive中移除
SQL> alter flashback archive fla1 remove tablespace fda4;
Flashback archive altered.
-- 注意，这里移除的仅仅是Flashback Archive中的信息，表空间不会被删除。
6.2.2.8 清空Flashback Archive中的所有历史记录
SQL> alter flashback archive fla1 purge all;
Flashback archive altered.
6.2.2.9 清空Flashback Archive 中超过1天的历史数据
SQL> alter flashback archive fla1 purge before timestamp (systimestamp - interval '1' day);
Flashback archive altered.
6.2.2.10 清空Flashback Archive 中指定SCN 之前的所有历史数据
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1315755078
SQL> alter flashback archive fla1 purge before scn 1315755078;
Flashback archive altered.
我这里只是演示一个SCN。 具体要结合自己的情况。
6.2.3 Dropping a Flashback Data Archive
Drop a Flashback Data Archive with the DROP FLASHBACK ARCHIVE statement.
Dropping a Flashback Data Archive deletes its historical data, but does not drop
its tablespaces.
-- 删除Flashback Archive 不会删除相应的表空间
示例：
SQL> DROP FLASHBACK ARCHIVE fla2;
Flashback archive dropped.
SQL> select flashback_archive_name name, status from dba_flashback_archive;
NAME STATUS
---------- -------
FLA1
6.2.4 Specifying the Default Flashback Data Archive
By default, the system has no default Flashback Data Archive.
If you are logged on as SYSDBA, you can specify default Flashback Data Archive
in either of these ways:
--默认情况下，没有default Flashback Data Archive. 当以sysdba 登陆之后，就可以指定它。
6.2.4.1 修改已经存在的Flashback Archive 为default
SQL> alter flashback archive fla1 set default;
Flashback archive altered.
SQL> alter flashback archive fla10 set default;
alter flashback archive fla10 set default
*
ERROR at line 1:
ORA-55605: Incorrect Flashback Archive is specified
如果指定的Flashback 不存在，就报错。
6.2.4.2 在创建Flashback Data Archive 时，指定default
SQL>create flashback archive default fla2 tablespace tbs1 quota 10m retention 1 year;
The default Flashback Data Archive for the system is the default Flashback Data Archive
for every user who does not have his or her own default Flashback Data Archive.
6.2.5 Enabling and Disabling Flashback Data Archive
By default, flashback archiving is disabled for any table.
You can enable flashback archiving for a table if you have the FLASHBACK ARCHIVE
object privilege on the Flashback Data Archive to use for that table.
默认情况下，所有表都没有启动flashback archive。
To enable flashback archiving 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 archiving for a table, but AUM(automatic undo managed) is disabled,
error ORA-55614 occurs when you try to modify the table.
ORA-55614: AUM needed for transactions on tracked tables
Cause: An attempt was made to execute DML on a tracked table without enabling Auto Undo Management.
Action: Disable tracking on the table or enable Auto Undo Management.
If a table has flashback archiving enabled, and you try to enable it again with a
different Flashback Data Archive, an error occurs
After flashback archiving 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 archiving for a table, specify NO FLASHBACK ARCHIVE in the
ALTER TABLE statement. (It is unnecessary to specify NO FLASHBACK ARCHIVE
in the CREATE TABLE statement, because that is the default.)
示例：
6.2.5.1 创建table，使用默认的Flashback Data Archive 来存储历史数据
SQL> create table test1 (id number) flashback archive;
Table created.
6.2.5.2 创建table，使用指定的Flashback Data Archive 来存储历史数据
SQL> create table test2 (id number) flashback archive fla1;
Table created.
6.2.5.3 对表启用Flashback archive，并使用默认的Flashback archive。
SQL> alter table test3 flashback archive;
Table altered.
6.2.5.4 禁用表的Flashback Archive
SQL> alter table test3 no flashback archive;
Table altered.
6.2.5.5 对table 启用Flashback archive，并指定Flashaback Archive 区。
SQL> alter table test3 flashback archive fla1;
Table altered.
6.2.6 DDL Statements on Tables Enabled for Flashback Data Archive
Flashback Data Archive supports many DDL statements, including some that
alter the table definition or move data. For example:
--启动Flashback Data Archive的表支持以下的DDL 操作
（1）ALTER TABLE statement that does any of the following:
1）Adds, drops, renames, or modifies a column
2）Adds, drops, or renames a constraint
3）Drops or truncates a partition
or subpartition operation
（2）TRUNCATE TABLE statement
（3）RENAME statement that renames a table
Some DDL statements cause error ORA-55610 when used on a table enabled for
Flashback Data Archive. For example:
-- 启动Flashback Data Archive的表上的一些DDL 操作可能触发ORA-55610的错误，
这些DDL 如下：
（1）ALTER TABLE statement that includes an UPGRADE TABLE clause,
with or without an INCLUDING DATA clause
（3）ALTER TABLE statement that moves or exchanges a partition
or subpartition operation
（3）DROP TABLE statement
ORA-55610: Invalid DDL statement on history-tracked table
Cause: An attempt was made to perform certain DDL statement that is disallowed on tables
that are enabled for Flashback Archive.
Action: No action required.
If you must use unsupported DDL statements on a table enabled for Flashback Data Archive,
use the DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA procedure to disassociate the base table
from its Flashback Data Archive.
To reassociate the Flashback Data Archive with the base table afterward,
use the DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA procedure.
-- 如果必须在已经启用Flashback Archive的表上执行这些不支持的DDL 操作，
可以用DBMS_FLASHBACK_ARCHIVE 包将表从Flashback Data Archive 分离出来，
待操作结束后再添加进去。
The DBMS_FLASHBACK_ARCHIVE package contains two simple procedures for disassociation
and reassociation of a Flashback Data Archive (FDA) enabled table from/with
its underlying FDA respectively.
在Flashback Area中，会有一张历史表记录着我们启动FA表的所有操作。
我们可以通过如下SQL 来查看他们之间的映射关系。
SQL> SELECT table_name,archive_table_name,status from dba_flashback_archive_tables;
TABLE_NAME ARCHIVE_TABLE_NAME STATUS
---------- -------------------- --------
test1 SYS_FBA_HIST_78429 ENABLED
test2 SYS_FBA_HIST_78431 ENABLED
ORA SYS_FBA_HIST_78448 ENABLED
test3 SYS_FBA_HIST_78456 ENABLED
HUAINING SYS_FBA_HIST_78464 ENABLED
QS SYS_FBA_HIST_78472 ENABLED
FA SYS_FBA_HIST_78484 ENABLED
7 rows selected.
我们要执行那些不支持的DDL，就需要用dbms_flashback_archive禁用他们之间的映射关系，
在操作，操作完在用该包启用他们。
关于dbms_flashback_archive包的使用，参考官网：
DBMS_FLASHBACK_ARCHIVE
Oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_flashb_archive.htm#ARPLS72464
示例：
SQL> drop table test1;
drop table test1
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
这个表使我们之前创建的，���启用了Flashback Archive.
表的分离和重新结合：
SQL> exec dbms_flashback_archive.disassociate_fba('SYS','test1');
PL/SQL procedure successfully completed.
SQL> exec dbms_flashback_archive.reassociate_fba('SYS','test1');
PL/SQL procedure successfully completed.
最后我们再分离，再drop table：
SQL> exec dbms_flashback_archive.disassociate_fba('SYS','test1');
PL/SQL procedure successfully completed.
SQL> drop table test1;
drop table test1
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
drop 失败。
google 一下，说是bug：9650074
9650074 ORA-55633 in Flashback data archive DDL support area。
ORA-55633: Cannot do DDL on Flashback Data Archive enabled table
Cause: An attempt was made to do DDL on tracked tables while one DDL is runnning.
Action: Retry the DDL at a later time.
6.3 一个用Flashback Data Archive 恢复数据的测试
这个测试使用之前的Flashback Archive: fla1.
创建测试表：
SQL> create table fa(id number) flashback archive;
Table created.
插入数据：
SQL> declare
2 i number;
3 begin
4 for i in 1..100 loop
5 insert into fa values(i);
6 end loop;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select count(*) from fa;
COUNT(*)
----------
100
查询时间：
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') tm from dual;
TM
-------------------
2011-05-11 15:33:35
再update 一次数据：
SQL> update fa set id=200 where id <50;
49 rows updated.
SQL>commit;
再查询一次时间：
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') tm from dual;
TM
-------------------
2011-05-11 15:35:23
使用Flashback Archive查询1分钟之前的数据：
SQL> select count(*) from fa as of timestamp (systimestamp - interval '1'minute);
COUNT(*)
----------
100
使用Flashback Archive查询10分钟之前的数据：
SQL> select count(*) from fa as of timestamp (systimestamp - interval '10'minute);
COUNT(*)
----------
0
这里显示为0. 因为我们还没有做DML 操作。
或者使用时间来查：
SQL> select count(*) from fa as of timestamp to_timestamp('2011-05-11 15:35:23','yyyy-mm-dd hh24:mi:ss');
COUNT(*)
----------
100
SQL> delete from fa;
100 rows deleted
SQL> commit;
Commit complete.
SQL> select count(*) from fa as of timestamp (systimestamp - interval '1'minute);
COUNT(*)
----------
100
根据时间的不同，查询的结果也不一样。 下面我们来确认下这个问题：
SQL> SELECT * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHI ARCHIVE_TABLE_NAME STATUS
---------- ---------- --------------- -------------------- --------
FA SYS FLA1 SYS_FBA_HIST_78484 ENABLED
从这个结果，可以看出，在Flashback archive对应的FA表的历史表是SYS_FBA_HIST_78484。
该表保存了FA表的所有的操作记录：
SQL> select count(*) from SYS_FBA_HIST_78484;
COUNT(*)
----------
149
SQL> desc SYS_FBA_HIST_78484
Name Null? Type
----------------------------------------- -------- ---------------
RID VARCHAR2(4000)
STARTSCN NUMBER
ENDSCN NUMBER
XID RAW(8)
OPERATION VARCHAR2(1)
ID NUMBER
注意一点：我们不能对这些历史表做任何修改操作，只能查询。
如果想对这些历史表进行相关的修改操作，和之前的操作一样：
使用dbms_flashback_archive分离2个表之间的关系。
如：
sql> exec dbms_flashback_archive.disassociate_fba('scott','emp_test');
sql> exec dbms_flashback_archive.reassociate_fba('scott','emp_test');
闪回归档：
实验：闪回归档
1.create tablespace arch_tbs datafile '/u01/app/oracle/oradata/PROD/arch_tbs.dbf' size 100m autoextend on maxsize 1G;
2.create user archive_admin identified by archive_admin default tablespace arch_tbs;
3.grant dba,flashback archive administer to archive_admin;
4.conn archive_admin/archive_admin
create flashback archive fda1 tablespace arch_tbs quota 10m retention 1 year;
5.alter flashback archive fdb1 set default;
--sys用户下
6.alter table test_user1.emp flashback archive;
7.select * from test_user1.emp as of timestamp(timestamp - interval '20' minute);