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;