• Oracle 10g通过创建物化视图实现不同数据库间表级别的数据同步


    摘自:http://blog.csdn.net/javaee_sunny/article/details/53439980

    目录(?)[-]

    1. Oracle 10g 物化视图语法如下
    2. 实例演示
      1. 主要步骤
        1. 在A节点创建原表和物化视图日志
        2. 在B节点创建连接A节点的远程链接
        3. 在B节点处创建目标表和与目标表名称相同的物化视图
        4. 在B节点处刷新物化视图
        5. 升级采用存储过程定时任务JOB方式定时刷新物化视图
        6. 进一步优化
      2. 文章更新记录
      3. 参考文章

    Oracle 10g 物化视图语法如下:

    create materialized view [view_name]
    refresh [fast|complete|force]
    [
    on [commit|demand] |
    start with (start_time) next (next_time)
    ]
    as
    {创建物化视图用的查询语句}

    以上是Oracle创建物化视图(Materialized View,以下简称MV)时的常用语法,各参数的含义如下:

    1. refresh [fast|complete|force] 视图刷新的方式:

      fast: 增量刷新.假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据。
            为了记录这种变化,建立增量刷新物化视图还需要一个物化视图日志表。
            create materialized view log on (主表名)。(多张表时,此语句也生效,创建后,原来的表中会多出两类视图表:MLOG$_table_name和RUPD$_table_name)
      
      complete: 全部刷新。相当于重新执行一次创建视图的查询语句。
      
      force: 这是默认的数据刷新方式。当可以使用fast模式时,数据刷新将采用fast方式;否则使用complete方式。

      2.MV数据刷新的时间:

    2. n demand: 在用户需要刷新的时候刷新,这里就要求用户自己动手去刷新数据(可以使用JOB定时刷新);
      on commit: 当主表中有数据提交的时候,立即刷新MV中的数据;
      start ……:从指定的时间开始,每隔一段时间(由next指定)就刷新一次;
      
      全刷新mv_test物化视图:
      begin
           dbms_mview.refresh(TAB=>'MV_TEST',
                              METHOD=>'COMPLETE',
                              PARALLELISM=>8);
      end;
      /

      增量刷新:

    begin
         dbms_mview.refresh(TAB=>'MV_TEST',
                            METHOD=>'FAST',
                            PARALLELISM=>1);
    end;
    /

                或者,也可以这样执行:

    exec dbms_mview.refresh('MV_TEST','F');
    
    dbms_mview.refresh('表名', 'F')   --快速刷新,也就是增量刷新
    dbms_mview.refresh('表名', 'C')   --完全刷新

    实例演示:

    以下是我通过创建物化视图,实现不同数据库间表级别的数据同步的一个过程记录,(两台服务器A和B,其中要把数据从A节点的表zh_major_item同步到B节点上)。目前实现同步的过程还只是一个单向的过程,也就是从A节点 —> B节点:

    主要步骤:

    1. 在A节点创建原表和物化视图日志
    2. 在B节点创建连接A节点的远程链接
    3. 在B节点处,创建目标表和与目标表名称相同的物化视图

    1. 在A节点创建原表和物化视图日志:

    --在源A处 创建物化视图日志
    sqlplus tianzhi_smart/tianzhi_smart@localhost:1521/orcl
    
    --原表zh_major_item之前已经存在,这里就不赘述表创建的过程了
    --创建物化视图日志
    create materialized view log on zh_major_item;

    2. 在B节点创建连接A节点的远程链接:

    --在目标B处 创建远程连接
    --如果没有创建远程连接的权限,需要登录sysdba,为用户授权;
    
    --之所以跟上面一样,因为我的用户名和密码相同而已
    sqlplus tianzhi_smart/tianzhi_smart@localhost:1521/orcl
    
    --创建远程连接 db_link
    create public database link db_link_A 
        connect to tianzhi_smart 
        identified by "tianzhi_smart" 
        using '192.168.56.6:1521/ORCL';
    
    --验证是否创建成功
    select * from zh_major_item@db_link_A ;

    3. 在B节点处,创建目标表和与目标表名称相同的物化视图:

    --B节点处创建目标表bc_major_item
    create table bc_major_item 
    as select * from zh_major_item@db_link_A where 1=2;
    
    --通过远程连接向A节点建立一个与目标表(bc_major_item)名称相同的物化视图    
    --使用on prebuilt table注册新的物化视图,注意view名称必须和表名称一样
    --使用on prebuilt table创建的物化视图被删除后,原来的表不被删除
    --注意这里我创建是refresh fast on demand 类型的物化视图
     create materialized view bc_major_item 
     on prebuilt table refresh fast on demand 
     as select * from zh_major_item@db_link_A ;

    4.在B节点处,刷新物化视图

     --刷新物化视图
     exec dbms_mview.refresh('bc_major_item ','C');
    
     --刷新后,查询目标表,比对是否和原表数据相同
     select * from bc_major_item ;

    5.升级采用存储过程+定时任务JOB方式,定时刷新物化视图

     --创建存储过程,进行增量刷新
        create or replace procedure refresh_bc_major_item
        as
        begin
            dbms_mview.refresh('bc_major_item','F');
        end;
        /
    
        --创建任务JOB
        SQL> variable job1 number;
        SQL>begin
     dbms_job.submit(:job1,'refresh_bc_major_item;',sysdate,'sysdate+1/(86400)');  
        end;
        /
    
        --运行JOB
        SQL> begin
           dbms_job.run(:job1);
          end;
          /

    6.进一步优化:

    可以再写一个存储过程+定时任务JOB,定时清空物化视图日志。

    文章更新记录

    v1.0 – 表面是实现了同步,其实查询的只是视图,并没有完成如题目所指的问题 
    v2.0 – 将目标表和物化视图建立联系,真正实现数据同步。2016.12.6

    参考文章:

    1. Oracle 11g 通过创建物化视图实现不同数据库间的表数据同步
    2. oracle中使用物化视图实现表的同步
     
     ============================================================================
    老哥指导:
    (1)预建表得加主键,预建表创建物化视图时加rowid会报错,使用预建表时就不能用with rowid
             使用预建的表局限性大,只能用with primary key,不能用with rowid
            预建表:是事先创建一个表后面用来存放物化视图数据的表
    (2)创建物化视图语句(可修改的物化视图)
    CREATE MATERIALIZED VIEW LOG ON BATCH_STEP_EXECUTION WITH ROWID ;
    CREATE MATERIALIZED VIEW MV_BATCH_STEP_EXECUTION 
    TABLESPACE ETL
    NOLOGGING 
    COMPRESS FOR OLTP
    REFRESH FAST WITH ROWID FOR UPDATE
    ENABLE QUERY REWRITE 
    AS SELECT * FROM BATCH_STEP_EXECUTION@ETL_55_AP cu

    如图:

    (3)

    登录hr用户下雨欣,ON PREBUILT TABLE子句允许咱们将现有已经存在的堆表注册为预初始化的物化视图里,

    这种设计多数在数仓环境,物化视图被删除,可以通过预建表进行恢复

    WITH REDUCED PRECISION就类似个授权,如果表或物化视图列的精度与子查询返回的精度不匹配,将导致精度缺失,给个关键字控制一下

     
  • 相关阅读:
    c++Primer再学习(1)
    c++Primer再学习练习Todo
    感悟(一)
    新目标《C++程序设计原理与实践》
    C++Primer再学习(4)
    开篇
    C++Primer再学习(3)
    C++实现的单例模式的解惑
    使用springboot缓存图片
    springboot h2 database
  • 原文地址:https://www.cnblogs.com/xinxin1994/p/7562473.html
Copyright © 2020-2023  润新知