使用物化视图解决GoldenGate不能使用中文表名问题

本文介绍了在Oracle数据库中创建和使用物化视图的方法,包括解决列名重复的问题、设置物化视图的日志、刷新策略及排除特定表等操作细节。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

源端:

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 "学生";

 

转载于:https://www.cnblogs.com/liang545621/p/7529177.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值