物化视图数据同步到mysql_oracle基于物化视图的数据同步

环境准备：

源：166.166.1.1：ydb:test

目标：166.166.1.2：tdb：test_ext

需求：

要求源表每天同步到目标数据库test_ext用户下

实现方法：

两个用户之间通过dblink做物化视图

1.2上创建用户：

create tablespace ts_test_ext datafile '/home/oracle/app/oradata/orcl/ts_test_ext01.dbf' size 30G autoextend on;

create user test_ext identified by xxxx default tablespace ts_test_ext;

grant connect,recource to test_ext;

操作过程：

1. 先给两边的用户全部以下授权：

1.1

grant create session to test;

grant create any context to test;

grant create synonym to test;

grant create public synonym to test;

grant create materialized view to test;

grant on commit refresh to test;

---------------------------

1.2

grant create session to test_ext;

grant create any context to test_ext;

grant create synonym to test_ext;

grant create public synonym to test_ext;

grant create materialized view to test_ext;

grant on commit refresh to test_ext;

grant create database link to test_ext;

2. 在目标数据库创建dblink连接到源库

在

create database link ydb connect to test identified by xxxx using 'ydb';

注：##在创建完dblink后或者之前先在目标库的tnsname.ora的配置文件里面写好源的service_name。

3. 在源数据库创建物化视图日志

1.1，test账号下

create materialized view log on test_table with primary key including new values;

4. 在目标数据库创建物化视图

1.2 test_ext

create materialized view test_table

BUILD deferred

refresh fast with primary key

on demand

enable QUERY REWRITE

as

select * from test_table@ydb;

注：

build immediate 在创建物化视图的同事根据主表生产数据，默认选项

build deferred 在创建物化视图的同时，在物化视图内部生成数据，如果此时没有生成数据，以后可以使用dbms包刷新

exec dbms_mview.refresh('test_table','C'),注意必须使用全量刷新，默认是增量刷新，所以这个参数必须是C，

刷新方式有complete fast force

5. 在物化视图上创建索引

create index inx_cell_phonehash on test_table(stan_cell_phone_hash) nologging ;

create index inx_c1_phonehash on test_table(stan_c_1_phone_hash) nologging;

#############################################

#################################################

制定刷新任务

或者是在创建物化视图的时候就直接加条件，每5分钟同步一次。

SQL> exec dbms_mview.refresh('test_table','C');全量同步

PL/SQL procedure successfully completed

SQL> exec dbms_mview.refresh('test_table');增量同步

PL/SQL procedure successfully completed

#############################################

#################################################

注： 删除物化视图的操作：

查询物化视图刷新的时间：

1.2：test_ext

SELECT mview_name, last_refresh_date, fullrefreshtim, increfreshtim

FROM dba_mview_analysis

WHERE owner='test_ext';

刷新完以后查看刷新的数据：

select count(*) from test_table;

----------------------------------------------

---------------------------

创建存储过程，和job自动刷新

create or replace procedure auto_mv_refresh_proc

as

begin

dbms_mview.refresh('test_table');

end auto_mv_refresh_proc;

BEGIN

sys.dbms_scheduler.create_job(

job_name => '"SYS"."AUTO_MV_REFRESH_JOB"',

job_type => 'STORED_PROCEDURE',

job_action => '"test_ext"."AUTO_MV_REFRESH_PROC"',

repeat_interval => 'FREQ=DAILY;BYHOUR=4;BYMINUTE=0;BYSECOND=0',

start_date => systimestamp at time zone 'Asia/Shanghai',

job_class => '"DEFAULT_JOB_CLASS"',

auto_drop => FALSE,

enabled => TRUE);

END;

备注：日志和物化视图要分开删除

原库：DROP MATERIALIZED VIEW LOG ON SHSC_REQ_RES_LOG;

目标库：DROP MATERIALIZED VIEW SHSC_REQ_RES_LOG;