11gR2 新特性之一—Flashback archive data增强
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
该特性是11gR1就引入了，只是在11gR1中限制太多，本文主要是讲解11gR2中关于该特性
所增加的一些功能。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
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> insert into ht05 values(1,'aaaa'); 1 row created. SQL> insert into ht05 values(5,'eeee'); 1 row created. SQL> insert into ht05 values(2,'bbbb'); 1 row created. SQL> insert into ht05 values(3,'cccc'); 1 row created. SQL> insert into ht05 values(4,'dddd'); 1 row created. SQL> insert into ht05 values(6,'ffff'); 1 row created. SQL> insert into ht05 values(7,'gggg'); 1 row created. SQL> commit; 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. |
如下是摘自官方文档的说明，关于11gR2中新增加的特性：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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. |
下面来进行一系列关于上述表述的测试。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 |
'------添加列' 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 '----我靠 这么简单的一个操作需要要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 '-- 这个时间跟前面简直没法比' '#####下面来测试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> ; 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> 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> 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> 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 '-- 从上看，不支持约束的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 |
仍有部分测试我未做，这点交给大伙儿去做，呵呵。 |
|
1 |
最后根据前面所做的测试，进行一下简单的总结： |
|
1 2 3 4 |
<span style="color: #0000ff">1. 支持表列的add，rename，drop以及modify操作；</span> <span style="color: #0000ff">2. 支持分区表的truncate，drop操作(仅限于rang分区、list分区以及rang-list、list_rang复合分区），</span> <span style="color: #0000ff"> 不支持hash分区表的drop操作；</span> <span style="color: #0000ff">3. 支持约束的add，rename，drop操作，不支持modify操作。</span> |
|
1 2 3 |
总的来说，部分测试跟官方文档的描述有出入，另外就是大家可以也发现了，那就是如果使用了 flashback archive data特性，<span style="color: #ff0000">那么几乎所有ddl操作都非常慢之慢，简直是慢的出奇。</span> <span style="color: #ff0000">我们知道ddl也会产生锁，那么对于高并发负载较高的系统来讲，如此之慢的操作，我想有点够呛的。</span> |
|
1 |
<strong><span style="color: #0000ff">最后补充一点的是，为啥oracle的官方文档经常跟实际测试有出入呢？欺骗大众吗？ </span></strong> |
Leave a Reply
You must be logged in to post a comment.