示例:
在源和目标都创建一个不可见和虚拟列commission
SQL> create table system.test_ogg
2 (empid number, salary number, commission number INVISIBLE generated always as (salary * .05) VIRTUAL );
Table created.
SQL> alter table system.test_ogg
2 add constraint pk_test_ogg primary key (empid);
Table altered.
如果我们不设置set colInvisible on,则默认是看不到commission列的。
SQL> desc system.test_ogg
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NUMBER
SALARY
SQL> SET COLINVISIBLE ON
SQL> desc system.test_ogg
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPID NUMBER
SALARY NUMBER
COMMISSION (INVISIBLE) NUMBER
源端插入一列,commission列的值基于salary字段计算,另外,“SELECT *”不会列出不可见列
SQL> insert into system.test_ogg
2 values
3 (1001, 10000);
1 row created.
SQL> commit;
Commit complete.
SQL> select empid,salary,commission from system.test_ogg;
EMPID SALARY COMMISSION
---------- ---------- ----------
1001 10000 500
SQL> select * from system.test_ogg;
EMPID SALARY
---------- ----------
1001 10000
目标端的队列文件中已经有表结构信息,即使源和目标端表结构不同,也不再需要使用sourceDefs和AssumeTargetDefs参数。
下面是日志信息,已经获取到表结构信息
2015-12-25 07:53:07 INFO OGG-02756 The definition for table SYSTEM.TEST_OGG is obtained from the trail file.
Skipping invisible column COMMISSION in default map.
2015-12-25 07:53:07 INFO OGG-06511 Using following columns in default map by name: EMPID, SALARY.
2015-12-25 07:53:07 INFO OGG-06510 Using the following key columns for target table SYSTEM.TEST_OGG: EMPID.
目标端可以复制不可见列对应的数据:
SQL> select empid,salary,commission from system.test_ogg;
EMPID SALARY COMMISSION
---------- ---------- ----------
1001 10000 500