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

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

Materialized View(物化视图)是包括一个查询结果的数据库对像, 它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据,也可以称为快照。 这个基本上就说出了物化视图的本质,它是一组查询的结果


一. 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方式。


  1. MV数据刷新的时间:


on 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

相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
相关文章
|
6月前
|
Oracle 关系型数据库 流计算
Flink CDC不支持直接连接到Oracle ADG备库进行数据同步
Flink CDC不支持直接连接到Oracle ADG备库进行数据同步
105 1
|
3月前
|
存储 数据库
云数据库如何处理数据迁移和数据同步?
云数据库如何处理数据迁移和数据同步?
41 0
|
8月前
|
SQL Oracle 关系型数据库
物化视图(Oracle与PostgreSQL对比)
物化视图(Oracle与PostgreSQL对比)
|
6月前
|
数据库 流计算
Flink CDC的工作原理是通过监听数据库的binlog来实现实时数据同步的
Flink CDC的工作原理是通过监听数据库的binlog来实现实时数据同步的
160 1
|
6月前
|
DataWorks 关系型数据库 MySQL
DataWorks可以通过数据同步任务(DTS)实现OceanBase和其他数据库之间的实时数据同步
DataWorks可以通过数据同步任务(DTS)实现OceanBase和其他数据库之间的实时数据同步
85 2
|
存储 SQL Oracle
ORACLE物化视图
ORACLE物化视图
|
SQL 缓存 数据库
理论:第三章:索引使用的限制条件,sql优化有哪些,数据同步问题(缓存和数据库),缓存优化
理论:第三章:索引使用的限制条件,sql优化有哪些,数据同步问题(缓存和数据库),缓存优化
|
SQL Oracle 关系型数据库
Oracle各种版本下“示例数据库的创建”的创建
Oracle各种版本下“示例数据库的创建”的创建
447 0
|
SQL Oracle 关系型数据库
Oracle OS认证与密码文件认证(操作系统认证、口令文件认证、创建密码文件)
Oracle OS认证与密码文件认证(操作系统认证、口令文件认证、创建密码文件)
467 0
|
12天前
|
SQL 数据可视化 关系型数据库
轻松入门MySQL:深入探究MySQL的ER模型,数据库设计的利器与挑战(22)
轻松入门MySQL:深入探究MySQL的ER模型,数据库设计的利器与挑战(22)

热门文章

最新文章

推荐镜像

更多