數據同步方案：
--用DBLINK 創建與所需同步表的鏈接
------------------------------------------------------------------------------------------------
--參數
--dblink1 名稱
--csys用戶名稱
--**** 用戶密碼
--HOST 服務器地址
--CONNECT_DATA 實例
------------------------------------------------------------------------------------------------
--創建DBlink
create database link dblink1
connect to csys identified by ****
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.134)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl )
)
)';
-----------------------------------------------------------------------------------------------
--需要在該文件路徑下增加如下配置D:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\sqlnet.ora
DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.8)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
----------------------------------------------------------------------------------------------
--刪除DBlink
DROP DATABASE link dblink1;
--使用 說明 codevalue是表 DBlink1 是創建的DBlink
select *from codevalue@dblink1 ;
--查看數據庫中的所有DBlink的連接
select owner,object_name from dba_objects where object_type='DATABASE LINK';
-----------------------------------------------------------------------------------------------
--創建物化視圖
--同步的主表
--同步主表存在主鍵
--創建物化視圖日志
create materialized view log on csb1 with primary key;
--查詢物化視圖日志
select * from mlog$_csb1;
--同步主表不存在主鍵
--創建物化視圖日志
create materialized view log on csb1 with rowid;
-------------------------------------------------------------------------------------------------
--基表創建物化視圖（基於物化視圖定時刷新--10分鐘）
--存在主鍵
create materialized view mv_csb1 refresh fast start with sysdate next trunc(sysdate,'mi')+10/1440 as select * from csb1@dblink_cs.regress.rdbms.dev.us.oracle.com;
--不存在主鍵
create materialized view mv_csb1 refresh fast with rowid start with sysdate next trunc(sysdate,'mi')+10/1440 as select * from csb1@dblink_cs.regress.rdbms.dev.us.oracle.com;
--查詢物化視圖
select * from dba_jobs;
---------------------------------------------------------------------------------------------------------------
--創建物化視圖（基於JOB定時刷新）
將上述中的時間參數去掉
-- job定時任務
--創建procedure
create or replace procedure refresh_mv
as
begin
dbms_mview.refresh('mv_csb1');
end refresh_mv;
--在DBMS_JOB系統包中設置相關參數
--What值
begin refresh_mv;end;
--間隔
trunc(sysdate,'mi')+10/1440
--------------------------------------------------------------------------------------------------------------
