利用db_link创建物化视图数据同步到数据仓库

物化视图同步数 1. 用途 现在需要把生产数据库中的部分数据同步到数据仓库中，考虑到成本和便捷性，采用定期刷新物化视图的方

物化视图同步数 1.     用途

现在需要把生产数据库中的部分数据同步到数据仓库中，考虑到成本和便捷性，采用定期刷新物化视图的方式同步数据。

2.     整体实施思路

l   首先创建一个dblink,可以访问远程数据库。

l   在本地创建一个物化视图,存储远程数据表,当远程数据表有变化时,会定时刷新到物化视图中。

l   创建Oracle job定时刷新表

3.     需要同步的表如下

序号

表中文名称

表名

说明

1

订单表

OMORDER

订单主表,保存订单的基础信息

2

订单明细表

ORDERDETAIL

存放订单里商品的信息

4.1.4.    配置步骤

4.1 配置数据仓库中tns连接

testrac =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.122)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.123)(PORT = 1521))

    (LOAD_BALANCE = yes)

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = jscn)

    )

  )

 

 

测试：

[oracle@testrac1 goldengate]$ sqlplus sys/sys@ testrac as sysdba

SQL> select count(*) from SYS.GV_$INSTANCE

4.2 创建dblink

语法：

create public database link dblink_nameconnect to username identified by passwd using 'tns_name'

说明：

dblink_name ：是dblink名称

username：是远端的用户名

passwd：是远端的秘密

'tns_name' ：是tnsnames.ora中的连接字符串名称

 

示例如下：

 

SQL> create public database link testrac connect to jscn identified by jscn  using 'testrac';

SQL> select count(*) from SYS.GV_$INSTANCE@testrac;

 

  COUNT(*)

----------

  2

 

 

4.3 创建物化视图同步数据

4.3.1 在源用户上执行以下，创建物化视图日志

说明：

对于已定义主键的表，分别执行以下 sql：

    create materialized view log on 表名  withprimary key;  

                  对于未定义主键的表，，分别执行以下 sql：

     creatematerialized view log on 表名  with rowid;

         使用sql语句：

                   select 'creatematerialized view log on '||table_name||' withprimary key;' fromuser_tables;

create materialized view log on thh with primary key;

--这两个没有主键，特殊对待

create materialized view log on USER_ORDER_INTE with rowid;

4.3.2 在目标用户上执行以下 sql，创建物化视图

创建物化视图语法如下：

create materialized view [view_name]

refresh [fast|complete|force] with [primaryid | rowid]

[

on [commit|demand] |

start with (start_time) next (next_time)

]

as

{创建物化视图用的查询语句}

 

         使用sql语句：

select 'create materializedview '||table_name||'  refresh fast on demand  as select * fromthh@testrac;' fromuser_tables;

create materialized view CONSIGNEE_ADDRESS  refresh fast  on demand  as select * from thh@testrac;

create materialized view CUSTOMER  refresh fast  on demand  as select * from thh@testrac;

 

4.3.3    创建定时任务

定时任务放在每天凌晨3点执行任务。

BEGIN

   DBMS_MVIEW.refresh (

      LIST=> 'OMORDER,ORDERDETAIL,ORDERTRACER,THH,THH_SUB,VACCOUNTREASON,CONSIGNEE_ADDRESS,LOGISTICS_COMPANY,PRODUCT,PRODUCT_CATE_REL,PRODUCTCATEGORY,STORE,STORE_INFO,CUSTOMER,CUSTOMER_INFO,USER_ORDER_INTE,GIFT_CARDS,GIFT_CARDS_TYPE,GIFT_CARDS_LOG,PROMOTIONS_OFFERS,JOIN_PROMOTIONS,TEL_EMAIL_RECORD,dealcategory,YDORDER,YDORDERTRACER,PRODUCT_BRAND,THH_LOGS',

      METHOD        => 'F',

      PARALLELISM   => 1);

END;

更多Oracle相关信息见Oracle 专题页面 ?tid=12