goldengate（7）-Supported objects and operations for DML

对于DML操作来说，支持的数据库对象有表、视图、物化视图、序列

1.支持表，包括一般的表、IOT表、簇表

  1.1 IOT表（索引组织表：简言之，就是把数据和索引放在一个地方，这个地方的就是索引段，区别于普通表，数据放在数据段，索引放在索引段。索引组织表一定要有一个主键，表的数据在索引段是按序排列的。）

goldengate支持oracle 版本10.2以后的IOT表

CREATE TABLE test.xy110 (id NUMBER,NAEP NUMBER,NICK VARCHAR2(32),GDATE DATE,
PRIMARY KEY (id)) ORGANIZATION INDEX TABLESPACE users;

 1.2源端插入一条数据

 insert into test.xy110 VALUES(1,100,'AK47',SYSDATE);
 commit;

SQL> SELECT * FROM TEST.XY110;
 
        ID       NAEP NICK                             GDATE
---------- ---------- -------------------------------- -----------
         1        100 AK47                             2013-03-12
 1.3.目标端查看数据

SQL> SELECT * FROM TEST.XY110;
 
        ID       NAEP NICK                             GDATE
---------- ---------- -------------------------------- -----------
         1        100 AK47                             2013-03-12
 

目标端IOT表复制成功。

1.4 goldengate 不支持带MAPPING TABLE的IOT表     

 GoldenGate supports IOTs that are created with the MAPPING TABLE option, but it only captures changes made to the base IOT, not changes made to the mapping table.However, Oracle will maintain the mapping table on the target, if one is being used.

goldengate支持带MAPPING TABLE选项的IOT表，但extract进程不会捕获带MAPPING TABLE的IOT表，只捕获base IOT表

示例：

CREATE TABLE test.xy111
(id NUMBER,
NAEP NUMBER,
NICK VARCHAR2(32),
GDATE DATE,
PRIMARY KEY (id))
ORGANIZATION INDEX
MAPPING TABLE;

CREATE BITMAP INDEX
TEST.IND_XY111_NICK 
ON TEST.XY111(NICK);

源端出入一条数据

insert into test.xy111 VALUES(1,200,'MAPPING TABLE',SYSDATE);
commit;

extract抽取进程状态为ABENDED!

EXTRACT    S_EX_HR   Last Started 2013-03-12 06:08   Status ABENDED
Checkpoint Lag       00:04:41 (updated 00:03:57 ago)
Log Read Checkpoint  Oracle Redo Logs
                     2013-03-12 06:03:53  Seqno 147, RBA 15399936

日志报错：

2013-03-12 06:08:34  ERROR   OGG-01737  Failed to validate table TEST.XY111. This table is an IOT with mapping table and not supported by Extract. Remove this table from Extract's parameter file and restart Extract.

日志显示This table is an IOT with mapping table and not supported by Extract

并且提供了解决方法： Remove this table from Extract's parameter file and restart Extract.

1.5 IOTs that are stored in a compressed format are not supported (for example, in acompressed tablespace).
NOTE A compressed IOT is different from an IOT that has key compression defined withthe COMPRESS option. IOTs with key compression are supported.

 goldengate表示 IOT表不支持压缩，有两次含义，第一层，支持主键压缩

示例：

CREATE TABLE test.xy111
(id NUMBER,
NAEP NUMBER,
NICK VARCHAR2(32),
GDATE DATE,
PRIMARY KEY (id,naep))
ORGANIZATION INDEX
COMPRESS;

insert into test.xy111 VALUES(1,200,'COMPRESS',SYSDATE);
commit;

源端与目标端都能看到到这条数据

   第二层： 如果IOT表在压缩表空间内，将不支持压缩IOT表

示例：

create tablespace space_compress  datafile '+DATA/dbking/datafile/space_compress01.dbf' size 20M  default compress;

CREATE TABLE test.xy112
(id NUMBER,
NAEP NUMBER,
NICK VARCHAR2(32),
GDATE DATE,
PRIMARY KEY (id,naep))
ORGANIZATION INDEX
TABLESPACE space_compress

insert into test.xy112 VALUES(1,500,'COMPRESS TABLESPACE',SYSDATE);
commit;

源端插入数据，目标端也能看到.说明是支持压缩表空间的.

goldengate对IOT不是很好的支持，只提供有限的支持。

1.6 goldengate对簇表的支持

  cluster（簇表）表：就是基于有共同的字段创建成簇的形式，共享数据块的一组表.

goldengate支持簇表在oracle 9.2以后的版本，索引簇和哈希簇均支持，经过加密和压缩的簇表不支持。

     a. 支持索引簇

       示例：

create cluster test.xy114_xy115_cluster(idnum number(5))
size 1024
tablespace users;

create index test.xy114_xy115_cluster_idx
on cluster test.xy114_xy115_cluster;

create table test.xy114 (idnum number(5) primary key, tname varchar2(20),tax varchar2(10),kitl char(15))
cluster test.xy114_xy115_cluster(idnum);

create table test.xy115 (idnum number(5) primary key, tname varchar2(20),tax varchar2(10),kitl char(15))
cluster test.xy114_xy115_cluster(idnum);

源端插入数据：

SQL> select * from test.xy114;
 
 IDNUM TNAME                TAX        KITL
------ -------------------- ---------- ---------------
     1 xy114                abtt       b2
SQL> select * from test.xy115;
 
 IDNUM TNAME                TAX        KITL
------ -------------------- ---------- ---------------
     1 xy115                aaat       b4

目标端也看到数据了：

SQL> select * from test.xy114;
 
 IDNUM TNAME                TAX        KITL
------ -------------------- ---------- ---------------
     1 xy114                abtt       b2
SQL> select * from test.xy115;
 
 IDNUM TNAME                TAX        KITL
------ -------------------- ---------- ---------------
     1 xy115                aaat       b4

   b.支持哈希簇

    create cluster test.xy116_hash_clsuter(idno number(10))
pctused 80
pctfree 20
tablespace users
hash is idno
hashkeys 300;

create table test.xy116 (
idno number(10),
tname varchar2(20),
g_date date)
cluster test.xy116_hash_clsuter(idno);

alter table test.xy116 add constraint pk_idno primary key (idno);

源端插入数据：

insert into test.xy116 values (1,'king',sysdate);
commit;

SQL> select * from test.xy116;
 
       IDNO TNAME                G_DATE
----------- -------------------- -----------
          1 king                 2013-03-12
  

目标端也看到了这条数据

SQL> select * from test.xy116;
 
       IDNO TNAME                G_DATE
----------- -------------------- -----------
          1 king                 2013-03-12

  c.不支持压缩簇表

Encrypted and compressed clustered tables are not supported

goldengate对簇表也是提供了有限的支持.

2 支持视图，其实视图无需解释，视图是基于表的同步更新

（无需示例）

3.支持物化视图

（请查看(goldengate (8)--goldengate对物化视图的支持）

4.支持序列

（请查看goldengate（9）---goldengate对序列的支持（暂未发布））