两个oracle 10g 数据库，通过物化视图同步数据的具体实施流程

源库是 192.168.1.81

目标库是192.168.1.96

统计库用的服务器是 81

在统计库81上创建只读用户snap_query，用于查询
conn /as sysdba

create user snap_query identified by snap_query
default tablespace users
temporary tablespace temp;

grant connect to snap_query;
grant select on userb.test1 to snap_query;

为表建立实体化视图日志，要求这些表带有PRIMARY KEY
CREATE MATERIALIZED VIEW LOG
ON userb.test1
TABLESPACE ts12
WITH PRIMARY KEY
EXCLUDING NEW VALUES;

----------------------------
在测试库96上

以sys用户登陆，查看并修改job_queue_processes的值

SQL> show parameter spfile --先检查是否是spfile（服务器端初始化文件）
SQL> show parameter job_queue_process --如果为零用以下命令修改
SQL> alter system set job_queue_processes=10 scope=spfile;

创建表空间，用于存放物化视图内容（即test1）
create tablespace ts_event
datafile 'i:\ts_event_01.dbf' size 5000m,
'i:\ts_event_02.dbf' size 5000m;

创建用户
create user ts12_st identified by ts12_st_2010
default tablespace ts_event
temporary tablespace temp;

grant connect,resource,create snapshot to ts12_st;
grant create database link to ts12_st;

在tnsnames.ora中配置网络别名ts12tj连接到81

创建数据库链

conn ts12_st/ts12_st_2010

create database link DBLINK_SNAP_QUERY
connect to snap_query identified by snap_query
using 'ts12tj';

测试：

select event_id,test1YPE
from userb.test1@DBLINK_SNAP_QUERY where rownum<10

创建物化视图

CREATE MATERIALIZED VIEW test1_st2
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE
NEXT sysdate + 12/24
AS
SELECT
Event_ID,
EVENT_CODE,
CALL_TIME,
TELE_X,
TELE_Y,
test1YPE,
EVENT_CONTENT,
ROAD_CODE,
PLACE,
DIRECTION,
PLACE_X,
PLACE_Y,
UPDATE_TIME,
MAIN_ASSISTANT_ROAD
FROM userb.test1@DBLINK_SNAP_QUERY;

----------------------------
创建完毕后在统计库81上执行
grant select on mlog$_test1 to snap_query;

在81上检查需要更新情况：
select '数据量 '||count(0) from test1 union
select '需更新量 '||count(0) from mlog$_test1;

----------------------------
在96上手工刷新物化视图方法：
exec dbms_mview.refresh('test1_st');

公司环境测试300万条耗时1 小时

经过长时间（1年）运行后，日志表mlog$_test1可能需要move以便回收空间。

或者使用前台任务job来实现刷新
1、首先删除之前创建的物化视图(先做个备份)
drop MATERIALIZED VIEW mv_emp;

2、创建物化视图
CREATE MATERIALIZED VIEW mv_emp
AS SELECT * FROM scott.emp;

3、建立一个用来刷新物化视图的存储过程:

CREATE OR REPLACE PROCEDURE auto_refresh_mview_job_proc
AS
BEGIN
dbms_mview.REFRESH('mv_emp');
END;

4、用ORACLE 10g的scheduler每天12:00和19:00定期刷新物化视图(时间可以根据需求定义)

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'auot_refresh_mview_job',
job_type => 'STORED_PROCEDURE',
job_action => 'scott.auto_refresh_mview_job_proc',
start_date => SYSDATE,
repeat_interval => 'FREQ=DAILY; BYHOUR=12,19',
enabled => TRUE,
comments => 'Refresh materialized view mv_emp'
);
END;

ITPub博客

物化视图同步两个数据库间的数据

最新文章