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

物化视图同步数

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;