源端：
conn sh/sh
create table "学生" ("学号" number primary key,"姓名" varchar2(30))
*
ORA-00957: duplicate column name 重复列名
create table "学生" ("学号" number primary key, " 姓名" varchar2(30));
create materialized view log on "学生" with primary key;
create materialized view mv_student
refresh fast on commit
as
select "学生"."学号" as id, "学生"." 姓名" as name from "学生";
添加这个物化视图的附加日志
GGSCI (linux5.5) 1> dblogin userid goldengate, password goldengate
info trandata sh.mv_student
add trandata sh.mv_student
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
TEST1 TABLE
TEST4 TABLE
???? TABLE
MLOG$_???? TABLE
RUPD$_???? TABLE
MV_STUDENT TABLE
一个一个指定，不能用模糊匹配，否则物化视图的日志、中文表，物化视图也会复制过去
GGSCI (linux5.5) 3> edit params ext_sh
TABLE SH.test1;
TABLE SH.test4;
TABLE SH.mv_student;
GGSCI (linux5.5) 5> stop ext_sh
GGSCI (linux5.5) 7> start ext_sh
SQL> desc sh."学生"
Name Null? Type
----------------------------------------- -------- ----------------------------
???? NOT NULL NUMBER
???? VARCHAR2(30)
如果用模糊匹配：
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST1 TABLE
TEST4 TABLE
???? TABLE
BIN$6TRNVHJWkIHgQKjAqQoNFg==$0 TABLE
MLOG$_???? TABLE
RUPD$_???? TABLE
SQL> desc sh."学生"
ORA-00942: table or view does not exist
目标端
dblogin userid goldengate, password goldengate
add checkpointtable goldengate.rep_mv_ckpt
add replicat rep_mv,exttrail ./dirdat/d2,checkpointtable rep_mv_ckpt
GGSCI (linux5.5) 5> edit params rep_sh
mapexclude sh.mv_student
GGSCI (linux5.5) 7> stop rep_sh
GGSCI (linux5.5) 9> start rep_sh
GGSCI (linux5.5) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED REP_MV 00:00:00 00:04:37
REPLICAT RUNNING REP_OE 00:00:00 00:00:03
REPLICAT RUNNING REP_SH 00:09:26 00:00:03
REPLICAT RUNNING REP_T1 00:00:00 00:00:01
REPLICAT RUNNING REP_T401 00:00:00 00:00:01
REPLICAT RUNNING REP_T402 00:00:00 00:00:01
REPLICAT RUNNING REP_T403 00:00:00 00:00:01
GGSCI (linux5.5) 7> edit params rep_mv
REPLICAT rep_mv
SETENV (NLS_LANG="American_America.ZHS16GBK")
SETENV (ORACLE_SID=test)
USERID goldengate,PASSWORD goldengate
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
numfiles 5000
--HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt/rep_mv.dsc, APPEND, MEGABYTES 1000
ALLOWNOOPUPDATES
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
map sh.mv_student, target sh.mv_student;
GGSCI (linux5.5) 9> start rep_mv
初始化：
conn sh/sh
create table "学生" ("学号" number primary key, " 姓名" varchar2(30));
create or replace view mv_student
as
select "学生"."学号" as id, "学生"." 姓名" as name from "学生";
GGSCI (linux5.5) 10> alter replicat rep_mv, begin now 把以前没有执行的事务直接抛弃掉
源端：
conn sh/sh
insert into "学生" values (4,'张三4');
commit;
目标端
select * from "学生";