4.sql

oracle数据库高级复制脚本实例

--以物化视图管理员身份登陆物化视图站点
connect mvadmin/mvadmin@dbtest2 as sysdba;
--建立刷新组
begin 
dbms_refresh.make ( 
name =>'mvadmin.refresh',
list => '', 
next_date => sysdate,
interval => '/*10:Mins*/ sysdate + 10/(60*24)', 
implicit_destroy => false,
rollback_seg =>'', 
push_deferred_rpc => true,
refresh_after_errors => false); 
end;
/
--建立物化视图组需要与主体组的名字一样
begin
dbms_repcat.create_snapshot_repgroup(
gname => 'rep_test',
master => 'dbtest1',
propagation_mode => 'asynchronous');
end;
/
--创建实体化视图@后面是远程数据库连接名。如果该表没有主键则使用rowid来刷新
--创建物化视图
--对于只读物化视图，省略FOR UPDATE 语句
create materialized view reptest.rep1 refresh force with rowid FOR UPDATE as select * from reptest.rep1@dbtest1;
--将物化视图添加到物化视图组--对于只读物化视图，此步骤可以省略
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
gname => 'rep_test',
sname => 'reptest',
oname => 'rep1',
type => 'SNAPSHOT',
min_communication => TRUE);
END;
/
--向刷新组中添加复制对象： 
begin 
dbms_refresh.add (
name =>'mvadmin.refresh',
list =>'reptest.rep1',
lax => true); 
end; 
/
commit;