ITPub博客

首页 > 数据库 > Oracle > oracle基于物化视图的数据同步

oracle基于物化视图的数据同步

原创 Oracle 作者:pennymeng 时间:2020-07-21 16:24:36 0 删除 编辑

环境准备:

源: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; 




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69976387/viewspace-2705958/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle 11g OCP, Oracle 11g OCM, MySQL 5.7 OCP, A member of OCMU Oracle User Group, Certificate of Aptech Certified System Master

注册时间:2020-06-03

  • 博文量
    17
  • 访问量
    8415