看小溪的总结中有for update的物化视图，花了两天时间搞通了。

CREATE MATERIALIZED VIEW ... FOR UPDATE

找一台服务器，安装两个数据库实例 实例名分别为 orc1 mv1
创建时，选择安装sample 这样会把hr用户给创建上。
接着参考文档，做1 ~ 5步. 完全照般，实验成功。
对高级复制的概念有了个模糊的印象。
1.txt 2.txt 3.txt 4.txt 5.txt

1.txt

/************************* BEGINNING OF Setting Up orc1 **************************/

sqlplus system/oracle@orc1

CREATE USER repadmin IDENTIFIED BY repadmin;

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'repadmin');
END;
/

GRANT COMMENT ANY TABLE TO repadmin;
GRANT LOCK ANY TABLE TO repadmin;
GRANT SELECT ANY DICTIONARY TO repadmin;

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username => 'repadmin');
END;
/

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'repadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
/

CONNECT repadmin/repadmin@orc1

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
delay_seconds => 0);
END;
/

CONNECT SYSTEM/oracle@orc1

CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'proxy_mviewadmin',
privilege_type => 'proxy_snapadmin',
list_of_gnames => NULL);
END;
/

GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;

GRANT CREATE SESSION TO proxy_refresher;
GRANT SELECT ANY TABLE TO proxy_refresher;

/******************** end of Setting Up orc1 ***********/

2.txt

/**************** Setting Up mv1 ****************/

CONNECT system/oracle@mv1
CREATE USER mviewadmin IDENTIFIED BY mviewadmin;

BEGIN
DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
username => 'mviewadmin');
END;
/

GRANT COMMENT ANY TABLE TO mviewadmin;

GRANT LOCK ANY TABLE TO mviewadmin;

GRANT SELECT ANY DICTIONARY TO mviewadmin;

CREATE USER propagator IDENTIFIED BY propagator;

BEGIN
DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
username => 'propagator');
END;
/

CREATE USER refresher IDENTIFIED BY refresher;

GRANT CREATE SESSION TO refresher;

GRANT ALTER ANY MATERIALIZED VIEW TO refresher;
BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'mviewadmin',
privilege_type => 'receiver',
list_of_gnames => NULL);
END;
/

CONNECT system/oracle@mv1

CREATE PUBLIC DATABASE LINK orc1 USING 'orc1';

CONNECT mviewadmin/mviewadmin@mv1;

CREATE DATABASE LINK orc1
CONNECT TO proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

CONNECT propagator/propagator@mv1

CREATE DATABASE LINK orc1
CONNECT TO repadmin IDENTIFIED BY repadmin;

CONNECT mviewadmin/mviewadmin@mv1

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PURGE (
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
delay_seconds => 0,
rollback_segment => '');
END;
/

CONNECT mviewadmin/mviewadmin@mv1

BEGIN
DBMS_DEFER_SYS.SCHEDULE_PUSH (
destination => 'orc1',
interval => 'SYSDATE + 1/24',
next_date => SYSDATE,
stop_on_error => FALSE,
delay_seconds => 0,
parallelism => 0);
END;
/
CONNECT system/oracle@mv1

CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP (
username => 'proxy_mviewadmin',
privilege_type => 'proxy_snapadmin',
list_of_gnames => NULL);
END;
/

GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;

GRANT CREATE SESSION TO proxy_refresher;
GRANT SELECT ANY TABLE TO proxy_refresher;

/**************** End of Setting Up mv1 ****************/

3.txt

/************ Creating a Master Group ***********/
conn hr/oracle@orc1
create table emp as select * from employees;
alter table emp add primary key (employee_id);

CONNECT repadmin/repadmin@orc1

PAUSE Press to continue when the schema exists at all master sites.

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPGROUP (
gname => 'hr_repg');
END;
/

BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
gname => 'hr_repg',
type => 'TABLE',
oname => 'emp',
sname => 'hr',
use_existing_object => TRUE,
copy_rows => FALSE);
END;
/

-- ......

BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
sname => 'hr',
oname => 'emp',
type => 'TABLE',
min_communication => TRUE);
END;
/

-- ......

BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => 'hr_repg');
END;
/

4.txt

/********** Creating a Materialized View Group **********/

CONNECT hr/oracle@orc1

CREATE MATERIALIZED VIEW LOG ON hr.countries;
CREATE MATERIALIZED VIEW LOG ON hr.departments;
CREATE MATERIALIZED VIEW LOG ON hr.employees;
CREATE MATERIALIZED VIEW LOG ON hr.jobs;
CREATE MATERIALIZED VIEW LOG ON hr.job_history;
CREATE MATERIALIZED VIEW LOG ON hr.locations;
CREATE MATERIALIZED VIEW LOG ON hr.regions;

CREATE MATERIALIZED VIEW LOG ON hr.emp;

-- Step 2 If they do not already exist, then create the replicated schema and its database link.
CONNECT system/oracle@mv1

CREATE TABLESPACE demo_mv1
DATAFILE 'demo_mv1.dbf' SIZE 10M AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

CREATE TEMPORARY TABLESPACE temp_mv1
TEMPFILE 'temp_mv1.dbf' SIZE 5M AUTOEXTEND ON;

CREATE USER hr IDENTIFIED BY hr;

ALTER USER hr DEFAULT TABLESPACE demo_mv1
QUOTA UNLIMITED ON demo_mv1;

ALTER USER hr TEMPORARY TABLESPACE temp_mv1;

GRANT
CREATE SESSION,
CREATE TABLE,
CREATE PROCEDURE,
CREATE SEQUENCE,
CREATE TRIGGER,
CREATE VIEW,
CREATE SYNONYM,
ALTER SESSION,
CREATE MATERIALIZED VIEW,
ALTER ANY MATERIALIZED VIEW,
CREATE DATABASE LINK
TO hr;

-- 上面的忽略

CONNECT hr/oracle@mv1
CREATE DATABASE LINK orc1
CONNECT TO proxy_refresher IDENTIFIED BY proxy_refresher;

-- Step 3 Create the materialized view group.
CONNECT mviewadmin/mviewadmin@mv1
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
gname => 'hr_repg',
master => 'orc1',
propagation_mode => 'ASYNCHRONOUS');
END;
/

-- Step 4 Create the refresh group.

BEGIN
DBMS_REFRESH.MAKE (
name => 'mviewadmin.hr_refg',
list => '',
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => TRUE,
refresh_after_errors => FALSE);
END;
/

-- Step 5 Add objects to the materialized view group.
CREATE MATERIALIZED VIEW hr.emp_mv1
REFRESH FAST WITH PRIMARY KEY FOR UPDATE
AS SELECT * FROM hr.emp@orc1;

BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
gname => 'hr_repg',
sname => 'hr',
oname => 'emp_mv1',
type => 'SNAPSHOT',
min_communication => TRUE);
END;
/

-- Step 6 Add objects to the refresh group.
BEGIN
DBMS_REFRESH.ADD (
name => 'mviewadmin.hr_refg',
list => 'hr.emp_mv1',
lax => TRUE);
END;
/

5.txt

SQL> conn hr/oracle@mv1
已连接。
SQL> select tname from tab;

TNAME
------------------------------------------------------------
REGIONS
COUNTRIES
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY
EMP_DETAILS_VIEW
EMP_MV1
USLOG$_EMP_MV1

已选择10行。

SQL> select count(1) from emp_mv1;

COUNT(1)
----------
107

SQL> desc emp_mv1
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

SQL> select max(employee_id) from emp_mv1;

MAX(EMPLOYEE_ID)
----------------
206

SQL> insert into emp_mv1 (employee_id,last_name,email,hire_date,job_id)
2 select 207,last_name,email,hire_date,job_id from emp_mv1 where employee_id=
206;

已创建 1 行。

SQL> commit;

提交完成。

SQL> select count(1) from emp_mv1;

COUNT(1)
----------
108

SQL> select count(1) from hr.emp@orc1
SQL> /

COUNT(1)
----------
107

SQL>

CONNECT mviewadmin/mviewadmin@mv1
EXECUTE DBMS_REFRESH.REFRESH ('hr_refg');

SQL>
SQL> select count(1) from hr.emp@orc1
2 ;

COUNT(1)
----------
108