利用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 faston 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;