golendgate(31)
golendgate(31)--Mapping columns(列映射)
列映射实例:
相同数据库类型的列进行映射,不同的表字段不同进行映射,这个需要在replicat进程添加参数ASSUMETARGETDEFS,COLMAP,不需要一个源端的数据定义文件。
语法:
TABLE
我这里使用目的端repliat进程添加参数。
源端创建测试表
create table gis_test.baobao4 (id int primary key,name varchar2(20),text char(10),cust_order varchar2(10));
列名:
id
name
text
cust_order
目的端创建测试表
create table gis_test.baobao4 (id int primary key,name varchar2(20),text char(10),cust_order varchar2(10));
列名:
id
name
text
cust_order
目的端创建需要映射的表
create table gis_test.baobao5 (id int primary key,cust_name varchar2(20),text_conxt char(10),cust_num varchar2(10));
列名:
id ####该列不需要映射,参数选项USEDEFAULTS表示该列不需要映射。
cust_name
text_conxt
cust_num
表baobao4需要映射到表baobao5的列:
cust_name = name,
text_conxt = text,
cust_num = cust_order
源端extract抽取进程配置:
EXTRACT s_ex_mid
-- SETENV (NLS_LANG = CHINESE_CHINA.ZHS16GBK)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID = "dbking")
USERID [email protected], PASSWORD ggs
TRANLOGOPTIONS ARCHIVEDLOGONLY
-- TRANLOGOPTIONS [email protected], ASMPASSWORD grid sysdba
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS ALTARCHIVELOGDEST INSTANCE dbking /arch/archivelog
TRANLOGOPTIONS DBLOGREADERBUFSIZE 1048576
EXTTRAIL /u01/app/oracle/ggs/dirdat/trail/cc
TABLE gis_test.*;
-- SETENV (NLS_LANG = CHINESE_CHINA.ZHS16GBK)
SETENV (ORACLE_HOME = "/u01/app/oracle/product/11.2.0/dbhome_1")
SETENV (ORACLE_SID = "dbking")
USERID [email protected], PASSWORD ggs
TRANLOGOPTIONS ARCHIVEDLOGONLY
-- TRANLOGOPTIONS [email protected], ASMPASSWORD grid sysdba
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS ALTARCHIVELOGDEST INSTANCE dbking /arch/archivelog
TRANLOGOPTIONS DBLOGREADERBUFSIZE 1048576
EXTTRAIL /u01/app/oracle/ggs/dirdat/trail/cc
TABLE gis_test.*;
源端pump进程配置:
EXTRACT pump_un
USERID [email protected], PASSWORD ggs
RMTHOST 10.23.5.71, MGRPORT 7809
RMTTRAIL /u01/app/oracle/ggs/dirdat/rtrail/pp
PASSTHRU
TABLE gis_test.*;
USERID [email protected], PASSWORD ggs
RMTHOST 10.23.5.71, MGRPORT 7809
RMTTRAIL /u01/app/oracle/ggs/dirdat/rtrail/pp
PASSTHRU
TABLE gis_test.*;
目的端replicat进程配置:
REPLICAT rep_li
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/ggs/dirrpt/discard/rep.dsc, APPEND
USERID ggs, PASSWORD ggs
MAP gis_test.*, TARGET gis_test.*;
MAP gis_test.baobao4, TARGET gis_test.baobao5, COLMAP(USEDEFAULTS,
cust_name = name,
text_conxt = text,
cust_num = cust_order)
;
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/ggs/dirrpt/discard/rep.dsc, APPEND
USERID ggs, PASSWORD ggs
MAP gis_test.*, TARGET gis_test.*;
MAP gis_test.baobao4, TARGET gis_test.baobao5, COLMAP(USEDEFAULTS,
cust_name = name,
text_conxt = text,
cust_num = cust_order)
;
源端插入数据:
insert into gis_test.baobao4 values (1,'king','quanli','nnnn');
insert into gis_test.baobao4 values (2,'king','quanli','nnnn');
commit;
insert into gis_test.baobao4 values (2,'king','quanli','nnnn');
commit;
目的端表gis_test.baobao5成功复制该两条数据。
SQL> select * from gis_test.baobao5;
ID CUST_NAME TEXT_CONXT CUST_NUM
--------------------------------------- -------------------- ---------- ----------
1 king quanli nnnn
2 king quanli nnnn
ID CUST_NAME TEXT_CONXT CUST_NUM
--------------------------------------- -------------------- ---------- ----------
1 king quanli nnnn
2 king quanli nnnn
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21266384/viewspace-757821/,如需转载,请注明出处,否则将追究法律责任。
上一篇:
golendgate(30)
下一篇:
ORACLE RAC的后台进程
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%>
<%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%}%>
<%if(items[i].items.total > 5) { %>
<%}%>
<%}%>
<%=items[i].items.items[j].createtime%>
回复
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>: <%=items[i].items.items[j].content%>
还有<%=items[i].items.total-5%>条评论) data-count=1 data-flag=true>点击查看
<%}%>
转载于:http://blog.itpub.net/21266384/viewspace-757821/