create user mvadmin identified by
mvadmin;
begin
dbms_repcat_admin.
grant_admin_any_schema(username=> ’mvadmin’);
end;
/
grant comment any table to mvadmin;
grant lock any table to mvadmin;
grant select any dictionary to mvadmin;
2．注冊傳播方：
begin
dbms_defer_sys.register_propagator(
username => ’mvadmin’);
end;
/
3．公共數(shù)據(jù)庫連接。需要每個復制需要創(chuàng)建三個數(shù)據(jù)庫連接。公共數(shù)據(jù)庫連接指定數(shù)據(jù)庫的全局名稱：
create public database link ORACLSH using ’Oracle.shanghai.
com’;
Using子句后跟的是全局數(shù)據(jù)庫名或者是連接字符串。
create public database link ORACLSH using ’(description=
(address=(protocol=tcp)(host=127.0.0.1)(port=1521))
(connect_data=(service_name=oracl)))’
4．建立清除延遲事務隊列調度作業(yè)：
disconnect;
connect mvadmin/mvadmin@BJ;
begin
dbms_defer_sys.schedule_purge(
next_date => sysdate,
interval => ’/*1:hr*/ sysdate + 1’,
delay_seconds => 0,
rollback_segment => ’’);
end;
5．建立復制管理員mvadmin的數(shù)據(jù)庫連接：
create database link ORACLSH connect to proxy_bjOracle
identified by proxy_bjoralce
Connect to ... Identified by ...子句指明用什么用戶連接遠程數(shù)據(jù)庫
6．建立復制調度數(shù)據(jù)庫連接作業(yè)：
begin
dbms_defer_sys.schedule_push(
destination => ’ora92zjk’,interval => ’/*1:hr*/ sysdate + 1’,
next_date => sysdate,stop_on_error => false,
delay_seconds => 0,parallelism => 0);
end;
/
7．授予SHORACL用戶（對應SHORACL方案）相應的權限建立實體化視圖：
disconnect;
connect system/passwd@BJ;
grant alter session to crm;
grant create cluster to crm;
grant create database link to crm;
grant create sequence to crm;
grant create session to crm;
grant create synonym to crm;
grant create table to crm;
grant create view to crm;
grant create procedure to crm;
grant create trigger to crm;
grant unlimited tablespace to crm;
grant create type to crm;
grant create any snapshot to crm;
grant alter any snapshot to crm;
8．建立復制方案的數(shù)據(jù)庫連接：
disconnect;
connect SHORACL/SHORACL@BJ;
create database link ORACLSH connect to ORACL identified
by ORACL;
復制方案的數(shù)據(jù)庫連接和復制管理員的數(shù)據(jù)庫連接要和system用戶間里的對應公共數(shù)據(jù)庫連接使用相同的名字，在調度連接時將使用公共數(shù)據(jù)庫連接中指定的數(shù)據(jù)庫全局名或者連接字符串。
9．建立實體化視圖：
disconnect;
connect mvadmin/mvadmin@BJ;
create materialized view SHORACL.CREDIT_CARD refresh fast
wit h pr imar y key as sele ct * from ORA CL.
CREDIT_CARD@ORACLSH;
@后面是數(shù)據(jù)庫連接名。如果該表沒有主鍵則使用rowid來刷新。
create materialized view SHORACL. CREDIT_CARD refresh
fast with rowid as select * from ORACL. CREDIT_CARD@ORACLSH;
10．為多個視圖建立刷新組：
begin
dbms_refresh.make (
name => ’mvadmin.sh_refresh’,list => ’’,
next_date => sysdate,interval => ’sysdate + 1’,
implicit_destroy => false,rollback_seg => ’’,
push_deferred_rpc => true,refresh_after_errors => false);
end;
