创建Flashback Data Archive
用户需要授予dba或flashback archive administer系统特权。flashback archive administer系统特权包含：create flashback archive，alter flashback archive，drop flashback archive权利。SQL> select * from dba_sys_privs where privilege like '%FLASH%';GRANTEE PRIVILEGE ADM COM------------------------------ ---------------------------------------- --- ---SYS FLASHBACK ANY TABLE NO NODBA FLASHBACK ANY TABLE NO NOSYS FLASHBACK ARCHIVE ADMINISTER NO NODBA FLASHBACK ARCHIVE ADMINISTER NO NOMDSYS FLASHBACK ANY TABLE NO NOSQL> grant flashback archive administer to hr;SQL> create tablespace flash_tbs datafile '/u01/app/oracle/oradata/yb/flash01.dbf' size 300m autoextend on next 30m maxsize 5g;SQL> create flashback archive flash1 tablespace flash_tbs retention 4 year;
创建Flashback Data Archive：
SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/dbt/tbs101.dbf' size 2G;SQL> create flashback archive default fla1 tablespace tbs1 quota 1g retention 1 month;
修改Flashback Data Archive：
#设置默认的flashback data archiveSQL> alter flashback archive fla1 set default; #flashback data archive 添加表空间 SQL> alter flashback archive fla1 add tablespace tbs2 quota1g;SQL> alter flashback archive fla1 add tablespace tbs3;#修改配额SQL> alter flashback archive fla1 modify tablespace tbs3 quota 20G;#修改期限SQL> alter flashback archive fla1 modify retention 2 year;#移除一个表空间SQL> alter flashback archive fla1 remove tablespace tbs2;#purge历史数据SQL> alter flashback archive fla1 purge all;SQL> alter flashback archive fla1 purge before timestamp(systimestamp-inteval '1' day);SQL> alter flashback archive fla1 purge before scn 123456;
删除Flashback Data Archive：
SQL> drop flashback archive fla1;
开启、取消flashback data archive：
缺省情况下没有对表开启Flashback Data ArchiveSQL> create table test1( name varchar2(30),address varchar2(30)) flashback archive flash1;SQL> select * from dba_flashback_archive_tables;TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS------------------------- ------------------------- ------------------------------ ----------------------------------------------------- -------------TEST1 HR FLASH1 SYS_FBA_HIST_106929 ENABLEDSQL> create table test2( name varchar2(30),address varchar2(30)) ;SQL> alter table test2 flashback archive;SQL> select * from dba_flashback_archive_tables;TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS------------------------- ------------------------- ------------------------------ ----------------------------------------------------- -------------TEST1 HR FLASH1 SYS_FBA_HIST_106929 ENABLEDTEST2 HR FLASH1 SYS_FBA_HIST_106930 ENABLEDSQL> alter table test2 no flashback archive;SQL> drop flashback archive flash1;
Flashback Data Archive开启对DDL的支持：
只支持以下DDL语句：-alter table-truncate table-rename table如果使用了不受支持的DDL语句，可以先用dbms_flashback_archive.disassociate_fba过程使得表和flashback data archive分离，DDL操作完成后使用dbms_flashback_archive.reassociate_fba重新关联
示例：
SQL> create table test01(id int,name varchar2(20),job varchar2(20)) flashback archive fla1;Table created.SQL> insert into test01 values(1,'hello','tec');1 row created.SQL> commit;Commit complete.SQL> select to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS') FROM dual;TO_CHAR(SYSTIMESTAM-------------------2015-11-03 10:03:04SQL> update test01 set job='dev' where id=1;1 row updated.SQL> commit;Commit complete.SQL> select to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS') FROM dual;TO_CHAR(SYSTIMESTAM-------------------2015-11-03 10:06:04SQL> update test01 set job='dba' where id=1;1 row updated.SQL> commit;Commit complete.SQL> select to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS') FROM dual;TO_CHAR(SYSTIMESTAM-------------------2015-11-03 10:09:02SQL> select * from test01; ID NAME JOB---------- -------------------- -------------------- 1 hello dbaSQL> SELECT * FROM test01 AS OF TIMESTAMP TO_TIMESTAMP ('2015-11-03 10:06:04', 'YYYY-MM-DD HH24:MI:SS'); ID NAME JOB---------- -------------------- -------------------- 1 hello devSQL>