11gR2 新特性之一—Flashback archive data增强

原帖地址http://www.killdb.com/?p=359

该特性是11gR1就引入了，只是在11gR1中限制太多，本文主要是讲解11gR2中关于该特性
所增加的一些功能。
SQL> select name,description from v$bgprocess where name like 'F%';

NAME DESCRIPTION
----- ---------------------------------------------
FMON File Mapping Monitor Process
FBDA Flashback Data Archiver Process
FSFP Data Guard Broker FSFO Pinger
SQL> create flashback archive test_ht
 2 tablespace "ROGER"
 3 quota 100m
 4 retention 3 day
 5 /

Flashback archive created.

SQL> conn roger/roger
Connected.
SQL> create table ht05(id number primary key,name varchar2(8)) flashback archive test_ht;

Table created.

SQL>
SQL> insert into ht05 values(1,'aaaa');
insert into ht05 values(2,'bbbb');
insert into ht05 values(3,'cccc');
insert into ht05 values(4,'dddd');
SQL> insert into ht05 values(5,'eeee');
insert into ht05 values(6,'ffff');
insert into ht05 values(7,'gggg');
commit;
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>

Commit complete.
SQL> select * from ht05 order by id;

 ID NAME
---------- --------
 1 aaaa
 2 bbbb
 3 cccc
 4 dddd
 5 eeee
 6 ffff
 7 gggg

7 rows selected.
SQL> grant flashback archive administer to roger;

Grant succeeded.

SQL>

如下是摘自官方文档的说明，关于11gR2中新增加的特性：

Oracle Database 11g Release 2 (11.2) users can now use most DDL commands on tables that are
being tracked with Flashback Data Archive. This includes:

Add, Drop, Rename, Modify Column

Drop, Truncate Partition

Rename, Truncate Table

Add, Drop, Rename, Modify Constraint

For more complex DDL (for example, upgrades and split table), the Disassociate and Associate
PL/SQL procedures can be used to temporarily disable Total Recall on specified tables. The
Associate procedure enforces schema integrity after association; the base table and history
table schemas must be the same.

This feature makes it much easier to use the Total Recall option with complex applications
that require the ability to modify the schema.
下面来进行一系列关于上述表述的测试。

------添加列
SQL> alter table ht05 add (addr varchar2(20));

Table altered.

alert如下：
Sun Sep 04 01:26:53 2011
Starting background process FBDA
Sun Sep 04 01:26:53 2011
FBDA started with pid=33, OS id=8225 --FBDA进程启动了

------删除列
SQL> alter table ht05 drop column addr;

Table altered.

------重命名表名
SQL> rename ht05 to ht05_old;

Table renamed.

SQL> desc ht05_old
Name Null? Type
------------------------ -------- --------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(8)

------修改列
SQL> alter table ht05_old modify (name varchar2(6));

Table altered.

SQL> set timing on
SQL> alter table ht05_old modify (name varchar2(4));

Elapsed: 00:00:16.38
SQL> ----我靠 这么简单的一个操作需要要16s才能完成。
-------重命名列
SQL> alter table ht05_old rename column name to name_old;

Table altered.

Elapsed: 00:00:18.48

我们来看看正常情况下修改列：
SQL> create table ht06 as select * from ht05_old;

Table created.
SQL> alter table ht06 add constraint pk_ht06 primary key(id);

Table altered.
SQL> select count(*) from ht06;

 COUNT(*)
----------
 7
SQL> alter table ht06 modify (name varchar2(4));

Table altered.

Elapsed: 00:00:00.06
SQL> --这个时间跟前面简直没法比

#####下面来测试partition table

SQL> create table ht07 partition by hash(object_id) partitions 5
 2 as select * from dba_objects;

Table created.

SQL> alter table ht07 flashback archive test_ht;

Table altered.

SQL>
SQL> ;
 1 select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from
 2 dba_tab_partitions where TABLE_OWNER='ROGER'
 3* and table_name='HT07'
SQL> /

TABLE_OWNER TABLE_NAME PARTITION_NAME TABLESPACE_NAME
--------------- --------------- --------------- ---------------
ROGER HT07 SYS_P21 USERS
ROGER HT07 SYS_P22 USERS
ROGER HT07 SYS_P23 USERS
ROGER HT07 SYS_P24 USERS
ROGER HT07 SYS_P25 USERS
SQL> select * from dba_flashback_archive_tables;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
---------- -------------- ----------------------- ------------------- --------
HT05_OLD ROGER TEST_HT SYS_FBA_HIST_73555 ENABLED
HT07 ROGER TEST_HT SYS_FBA_HIST_73574 ENABLED

SQL>
SQL> set timing on
SQL> alter table ht07 truncate partition SYS_P25;

Table truncated.

Elapsed: 00:00:18.23
SQL> alter table ht07 drop partition SYS_P24;
alter table ht07 drop partition SYS_P24
 *
ERROR at line 1:
ORA-14255: table is not partitioned by Range, List, Composite Range or Composite List method

Elapsed: 00:00:06.43
SQL>
SQL> set timing off
SQL> create table ht08 partition by range(object_id)
 2 (partition ht08_p1 values less than (10000),
 3 partition ht08_p2 values less than (20000),
 4 partition ht08_p3 values less than (30000),
 5 partition ht08_p4 values less than (40000),
 6 partition ht08_p5 values less than (maxvalue)) as select * from ht07 where 1=1;

Table created.

SQL> alter table ht08 flashback archive test_ht;

Table altered.

SQL> set timing on
SQL> alter table ht08 truncate partition ht08_p5;

Table truncated.

Elapsed: 00:00:24.66
SQL> alter table ht08 drop partition ht08_p4;

Table altered.

Elapsed: 00:00:19.21
SQL> alter table ht08 add constraint pk_ht08 primary key(object_id);

Table altered.

Elapsed: 00:00:10.61
SQL>
SQL> alter table ht08 drop constraint PK_HT08;

Table altered.

Elapsed: 00:00:00.42
SQL> alter table ht08 add constraint pk_ht08 primary key(object_id);

Table altered.

Elapsed: 00:00:10.27
SQL> alter table ht08 rename constraint pk_ht08 to pk_ht08_old;

Table altered.

Elapsed: 00:00:00.17
SQL> alter table ht08 modify constraint disable validate ;
alter table ht08 modify constraint disable validate
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

Elapsed: 00:00:00.05
SQL> ---从上看，不支持约束的modify操作

SQL> alter table ht08 split partition ht08_p5 at (40000) into (partition HT08_P4,partition HT08_P5);
alter table ht08 split partition ht08_p5 at (40000) into (partition HT08_P4,partition HT08_P5)
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

Elapsed: 00:00:00.12
SQL> alter table ht08 add partition HT08_P4 values less than (40000);
alter table ht08 add partition HT08_P4 values less than (40000)
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table

Elapsed: 00:00:00.13

----从上看也不支持分区表的add和split操作。

仍有部分测试我未做，这点交给大伙儿去做，呵呵。

最后根据前面所做的测试，进行一下简单的总结：

1. 支持表列的add，rename，drop以及modify操作；
2. 支持分区表的truncate，drop操作(仅限于rang分区、list分区以及rang-list、list_rang复合分区），
 不支持hash分区表的drop操作；
3. 支持约束的add，rename，drop操作，不支持modify操作。

总的来说，部分测试跟官方文档的描述有出入，另外就是大家可以也发现了，那就是如果使用了
flashback archive data特性，那么几乎所有ddl操作都非常慢之慢，简直是慢的出奇。
我们知道ddl也会产生锁，那么对于高并发负载较高的系统来讲，如此之慢的操作，我想有点够呛的。

最后补充一点的是，为啥oracle的官方文档经常跟实际测试有出入呢？欺骗大众吗？