GoldenGate Column Conversion

源端extract参数,表结构

GGSCI (gg01) 82> VIEW params eorajj

EXTRACT EORAJJ
USERID system, PASSWORD oracle
RMTHOST gg02, MGRPORT 7809
RMTTRAIL /u01/app/oracle/goldengate/dirdat/jj

DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA,REPORT

TRANLOGOPTIONS CONVERTUCS2CLOBS

TABLE scott.stu;
SEQUENCE scott.*;


GGSCI (gg01) 83>

SCOTT@gg01:~>DESC stu
 Name                    NULL?    TYPE
 ----------------------- -------- ----------------
 STU_ID                  NOT NULL NUMBER(3)
 STU_NAME                         VARCHAR2(20)
 STU_EMAIL                        VARCHAR2(30)
 PHONE                            VARCHAR2(11)
 STU_HOBBY                        VARCHAR2(10)

SCOTT@gg01:~>

目标端replicat参数,表结构

GGSCI (gg02) 108> VIEW params rorajj

REPLICAT RORAJJ
USERID system, PASSWORD oracle
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/goldengate/dirrpt/RORAJJ.DSC, PURGE

DDL INCLUDE MAPPED
DDLERROR DEFAULT IGNORE RETRYOP
DDLOPTIONS REPORT

--映射目标端和源端的字段名称
COLMATCH NAMES STUDENT_ID = STU_ID           
COLMATCH NAMES STUDENT_NAME = STU_NAME
COLMATCH NAMES STUDENT_EMAIL = STU_EMAIL

--忽略STU_前缀
COLMATCH PREFIX STU_

--源端和目标端列明相同的使用COLMAP (USEDEFAULTS)函数自动匹配
MAP scott.stu, TARGET scott.student, COLMAP (USEDEFAULTS);
--关闭列映射
COLMATCH RESET

SCOTT@gg02:~>DESC student;
 Name                    NULL?    TYPE
 ----------------------- -------- ----------------
 STUDENT_ID              NOT NULL NUMBER(3)
 STUDENT_NAME                     VARCHAR2(10)
 STUDENT_EMAIL                    VARCHAR2(30)
 PHONE                            VARCHAR2(11)
 HOBBY                            VARCHAR2(10)

验证

SCOTT@gg01:~>INSERT INTO stu 
  2  VALUES
  3  (1,'Zhong','test@126.com','138140xxxxx','Ping Pang');

1 ROW created.

SCOTT@gg01:~>commit;

Commit complete.

SCOTT@gg01:~>SELECT * FROM stu;

    STU_ID STU_NAME             STU_EMAIL                      PHONE       STU_HOBBY
---------- -------------------- ------------------------------ ----------- ----------
         1 Zhong                test@126.com                   138140xxxxx Ping Pang

SCOTT@gg01:~>

目标端

SCOTT@gg02:~>SELECT * FROM student;

STUDENT_ID STUDENT_NA STUDENT_EMAIL                  PHONE       HOBBY
---------- ---------- ------------------------------ ----------- ----------
         1 Zhong      test@126.com                   138140xxxxx Ping Pang

SCOTT@gg02:~>