Oracle11g新特性：Flashback Data Archive续
参考文章:http://www.ningoo.net/html/2007/oracle11g_new_feature_flashback_data_archive2.html
一.后台进程 Oracle11g为Flashback data archive特性专门引入了一个新的后台进程FBDA，用于将追踪表(traced table，也就是将指定使用flashback data archive的table)的历史变化数据转存到闪回归档区。 NING@11g>select name,description from v$bgprocess where name=’FBDA’; NAME DESCRIPTION —————————— —————————————- FBDA Flashback Data Archiver Process 二.一些限制条件 1.Flashback data archive只能在ASSM的tablespace上创建 NING@11g>create flashback archive test_archive2 2 tablespace “TEST” 3 quota 1 m 4 retention 1 day; tablespace “TEST” * ERROR at line 2: ORA-55627: Flashback Archive tablespace must be ASSM tablespace 2.Flashback data archive要求必须使用自动undo管理 NING@11g>show parameter undo_management; NAME TYPE VALUE ———————————— ———- —————————— undo_management string MANUAL NING@11g>create flashback archive test_archive2 2 tablespace “TEST” 3 quota 1 m 4 retention 1 day; create flashback archive test_archive2 * ERROR at line 1: ORA-55628: Flashback Archive supports Oracle 11g or higher NING@11g>show parameter compatible NAME TYPE VALUE ———————————— ———- —————————— compatible string 11.1.0.0.0 三.空间配额 磁盘配额由quota子句指定，只能使用整数，单位可以是M/G/T/P。如果省略quota子句，则默认为unlimited，但这样则要求用户对于该tablespace必须有unlimited的空间配额，并且指定的quota必须小于用户在该tablespace上的配额限制，在我的试验中，只有flasback data archive的配额小于tablespace的配额84M以上才可以创建成功，否则失败，至于为什么会是84M，暂时还不清楚，我的系统是Redhat AS5，有兴趣的朋友请帮忙测试这个问题。昨天昏了头了，应该是该用户在该tablespace中已经占用了80多M的空间，所以会出现这个问题。 一个Flashback data archive可以使用多个tablespace作为存储空间，创建时指定的tablespace为第一个tablespace，后续还可以通过alter flashback archive … add tablespace增加新的tablespace进来，也可以通过later flashback archive … remove tablespace移除某个tablespace（不会物理删除该tablespace）。 SYS@11g>revoke unlimited tablespace from ning; Revoke succeeded. SYS@11g>alter user ning quota 85m on users; User altered. NING@11g>create flashback archive test_archive2 2 tablespace “USERS” 3 retention 1 day; tablespace “USERS” * ERROR at line 2: ORA-55621: User quota on tablespace “USERS” is not enough for Flashback Archive NING@11g>create flashback archive test_archive2 2 tablespace “USERS” 3 quota 2 m 4 retention 1 day; tablespace “USERS” * ERROR at line 2: ORA-55621: User quota on tablespace “USERS” is not enough for Flashback Archive NING@11g>create flashback archive test_archive2 2 tablespace “USERS” 3 quota 1 m 4 retention 1 day; Operation 218 succeeded. 但是空间配额可以超过该tablespace本身的物理大小 SYS@11g>drop tablespace test including contents and datafiles; Tablespace dropped. SYS@11g>create tablespace test 2 datafile ‘/oracle/oradata/ning/test01.dbf’ size 10m; Tablespace created. SYS@11g>alter user ning quota unlimited on test; User altered. NING@11g>create flashback archive test_archive3 2 tablespace “TEST” 3 quota 100 m 4 retention 1 month; Operation 218 succeeded. 四.空间不足会导致追踪表上的DML操作失败 NING@11g>create table test as select * from all_objects where 1=2; Table created. NING@11g>alter table test flashback archive test_archive1; Table altered. NING@11g>insert into test select * from all_objects; 11873 rows created. NING@11g>insert into test select * from test; … NING@11g>/ 379936 rows created. NING@11g>/ insert into test select * from test * ERROR at line 1: ORA-01536: space quota exceeded for tablespace ‘USERS’ 五.清除闪回归档区的数据 1.清除所有数据 NING@11g>alter flashback archive test_archive1 purge all; Operation 219 succeeded. 2.清除某个时间点，比如一天前的数据 NING@11g>alter flashback archive test_archive1 2 purge before timestamp (systimestamp – interval ’1′ day); Operation 219 succeeded. 3.清除某个SCN之前的历史数据 NING@11g>alter flashback archive test_archive1 2 purge before scn 8570685767554; Operation 219 succeeded. 六.置于Flashback data archive中的table的一些限制 追踪表(Tracked table)，也就是指定将历史数据保存到某个flashback data archive中的table，不能执行DDL操作(add column除外)。 NING@11g>drop table test; drop table test * ERROR at line 1: ORA-55610: Invalid DDL statement on history-tracked table NING@11g>truncate table test; truncate table test * ERROR at line 1: ORA-55610: Invalid DDL statement on history-tracked table NING@11g>alter table test drop column object_id; alter table test drop column object_id * ERROR at line 1: ORA-55610: Invalid DDL statement on history-tracked table NING@11g>alter table test add col_test int; Table altered. 但是可以rename table，这一点和文档上说的不一致 NING@11g>rename test to test1; Table renamed. NING@11g>select table_name,flashback_archive_name from dba_flashback_archive_tables; TABLE_NAME FLASHBACK_ARCHIVE_NAME —————————— —————————— TEST1 TEST_ARCHIVE1
