Oracle9i Replication Management API Reference Release 2 (9.2) Part Number A96568-01 |
|
This chapter illustrates how to create a materialized view group at a remote materialized view replication site. This chapter contains these topics:
Before you build materialized view environments, you must set up your master site, create a master group, and set up your intended materialized view sites. Also, if conflicts are possible at the master site due to activity at the materialized view sites you are creating, then configure conflict resolution for the master tables of the materialized views before you create the materialized view group.
After setting up your materialized view site and creating at least one master group, you are ready to create a materialized view group at a remote materialized view site. Figure 5-1 illustrates the process of creating a materialized view group.
See Also:
Chapter 2, "Create Replication Site" for information about setting up a materialized view site, and see Chapter 3, "Create a Master Group" for information about creating a master group. |
This chapter guides you through the process of creating two materialized view groups at two different materialized view sites: mv1.world
and mv2.world
:
mv1.world
is based on the objects in the hr_repg
master group at the orc1.world
master site.mv2.world
is based on the objects in the hr_repg
materialized view group at the mv1.world
materialized view site.Therefore, the examples in this chapter illustrate how to create a multitier materialized view environment, where one or more materialized views are based on other materialized views.
Complete the following steps to create these two materialized view groups.
/************************* BEGINNING OF SCRIPT ******************************
Complete the following steps to create the hr_repg materialized view group at the mv1.world
materialized view site. This materialized view group is based on the hr_repg
master group at the orc1.world
master site.
If you want one of your master sites to support a materialized view site, then you need to create materialized view logs for each master table that is replicated to a materialized view. Recall from Figure 2-1 that orc1.world
serves as the target master site for the mv1.world
materialized view site. The required materialized view logs must be created at orc1.world
.
*/ SET ECHO ON SPOOL create_mv_group.out CONNECT hr/hr@orc1.world 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; /*
See Also:
The |
*/ CONNECT system/manager@mv1.world 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; /*
Before building your materialized view group, you must make sure that the necessary database links exist for the replicated schema. The owner of the materialized views needs a database link pointing to the proxy_refresher
that was created when the master site was set up.
*/ CONNECT hr/hr@mv1.world CREATE DATABASE LINK orc1.world CONNECT TO proxy_refresher IDENTIFIED BY proxy_refresher; /*
See Also:
Step 7 for more information about creating proxy master site users |
The following procedures must be executed by the materialized view administrator at the remote materialized view site.
*/ CONNECT mviewadmin/mviewadmin@mv1.world /*
The master group that you specify in the gname
parameter must match the name of the master group that you are replicating at the target master site.
*/ BEGIN DBMS_REPCAT.CREATE_MVIEW_REPGROUP ( gname => 'hr_repg', master => 'orc1.world', propagation_mode => 'ASYNCHRONOUS'); END; / /*
All materialized views that are added to a particular refresh group are refreshed at the same time. This ensures transactional consistency between the related materialized views in 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; / /*
Whenever you create a materialized view, always specify the schema name of the table owner in the query for the materialized view. In the examples below, hr
is specified as the owner of the table in each query.
*/ CREATE MATERIALIZED VIEW hr.countries_mv1 REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.countries@orc1.world; CREATE MATERIALIZED VIEW hr.departments_mv1 REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.departments@orc1.world; CREATE MATERIALIZED VIEW hr.employees_mv1 REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.employees@orc1.world; CREATE MATERIALIZED VIEW hr.jobs_mv1 REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.jobs@orc1.world; CREATE MATERIALIZED VIEW hr.job_history_mv1 REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.job_history@orc1.world; CREATE MATERIALIZED VIEW hr.locations_mv1 REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.locations@orc1.world; CREATE MATERIALIZED VIEW hr.regions_mv1 REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.regions@orc1.world; /*
*/ BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => 'hr_repg', sname => 'hr', oname => 'countries_mv1', type => 'SNAPSHOT', min_communication => TRUE); END; / BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => 'hr_repg', sname => 'hr', oname => 'departments_mv1', type => 'SNAPSHOT', min_communication => TRUE); END; / BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => 'hr_repg', sname => 'hr', oname => 'employees_mv1', type => 'SNAPSHOT', min_communication => TRUE); END; / BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => 'hr_repg', sname => 'hr', oname => 'jobs_mv1', type => 'SNAPSHOT', min_communication => TRUE); END; / BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => 'hr_repg', sname => 'hr', oname => 'job_history_mv1', type => 'SNAPSHOT', min_communication => TRUE); END; / BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => 'hr_repg', sname => 'hr', oname => 'locations_mv1', type => 'SNAPSHOT', min_communication => TRUE); END; / BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => 'hr_repg', sname => 'hr', oname => 'regions_mv1', type => 'SNAPSHOT', min_communication => TRUE); END; / /*
All of the materialized view group objects that you add to the refresh group are refreshed at the same time to preserve referential integrity between related materialized views.
*/ BEGIN DBMS_REFRESH.ADD ( name => 'mviewadmin.hr_refg', list => 'hr.countries_mv1', lax => TRUE); END; / BEGIN DBMS_REFRESH.ADD ( name => 'mviewadmin.hr_refg', list => 'hr.departments_mv1', lax => TRUE); END; / BEGIN DBMS_REFRESH.ADD ( name => 'mviewadmin.hr_refg', list => 'hr.employees_mv1', lax => TRUE); END; / BEGIN DBMS_REFRESH.ADD ( name => 'mviewadmin.hr_refg', list => 'hr.jobs_mv1', lax => TRUE); END; / BEGIN DBMS_REFRESH.ADD ( name => 'mviewadmin.hr_refg', list => 'hr.job_history_mv1', lax => TRUE); END; / BEGIN DBMS_REFRESH.ADD ( name => 'mviewadmin.hr_refg', list => 'hr.locations_mv1', lax => TRUE); END; / BEGIN DBMS_REFRESH.ADD ( name => 'mviewadmin.hr_refg', list => 'hr.regions_mv1', lax => TRUE); END; / /*
Complete the following steps to create the hr_repg materialized view group at the mv2.world
materialized view site. This materialized view group is based on the hr_repg
materialized view group at the mv1.world
materialized view site.
If you want one of your master materialized view sites to support another materialized view site, then you need to create materialized view logs for each materialized view that is replicated to another materialized view site. Recall from Figure 2-1 that mv1.world
serves as the target master internalized view site for the mv2.world
materialized view site. The required materialized view logs must be created at mv1.world
.
*/ CONNECT hr/hr@mv1.world CREATE MATERIALIZED VIEW LOG ON hr.countries_mv1; CREATE MATERIALIZED VIEW LOG ON hr.departments_mv1; CREATE MATERIALIZED VIEW LOG ON hr.employees_mv1; CREATE MATERIALIZED VIEW LOG ON hr.jobs_mv1; CREATE MATERIALIZED VIEW LOG ON hr.job_history_mv1; CREATE MATERIALIZED VIEW LOG ON hr.locations_mv1; CREATE MATERIALIZED VIEW LOG ON hr.regions_mv1; /*
See Also:
The |
*/ CONNECT system/manager@mv2.world CREATE TABLESPACE demo_mv2 DATAFILE 'demo_mv2.dbf' SIZE 10M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE; CREATE TEMPORARY TABLESPACE temp_mv2 TEMPFILE 'temp_mv2.dbf' SIZE 5M AUTOEXTEND ON; CREATE USER hr IDENTIFIED BY hr; ALTER USER hr DEFAULT TABLESPACE demo_mv2 QUOTA UNLIMITED ON demo_mv2; ALTER USER hr TEMPORARY TABLESPACE temp_mv2; 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; /*
Before building your materialized view group, you must make sure that the necessary database links exist for the replicated schema. The owner of the materialized views needs a database link pointing to the proxy_refresher
that was created when the master materialized view site was set up.
*/ CONNECT hr/hr@mv2.world CREATE DATABASE LINK mv1.world CONNECT TO proxy_refresher IDENTIFIED BY proxy_refresher; /*
See Also:
Step 6 for more information about creating proxy master materialized view site users |
The following procedures must be executed by the materialized view administrator at the remote materialized view site.
*/ CONNECT mviewadmin/mviewadmin@mv2.world /*
The replication group that you specify in the gname
parameter must match the name of the replication group that you are replicating at the target master materialized view site.
*/ BEGIN DBMS_REPCAT.CREATE_MVIEW_REPGROUP ( gname => 'hr_repg', master => 'mv1.world', propagation_mode => 'ASYNCHRONOUS'); END; / /*
All materialized views that are added to a particular refresh group are refreshed at the same time. This ensures transactional consistency between the related materialized views in 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; / /*
Whenever you create a materialized view that is based on another materialized view, always specify the schema name of the materialized view owner in the query for the materialized view. In the examples below, hr
is specified as the owner of the materialized view in each query.
*/ CREATE MATERIALIZED VIEW hr.countries_mv2 REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.countries_mv1@mv1.world; CREATE MATERIALIZED VIEW hr.departments_mv2 REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.departments_mv1@mv1.world; CREATE MATERIALIZED VIEW hr.employees_mv2 REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.employees_mv1@mv1.world; CREATE MATERIALIZED VIEW hr.jobs_mv2 REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.jobs_mv1@mv1.world; CREATE MATERIALIZED VIEW hr.job_history_mv2 REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.job_history_mv1@mv1.world; CREATE MATERIALIZED VIEW hr.locations_mv2 REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.locations_mv1@mv1.world; CREATE MATERIALIZED VIEW hr.regions_mv2 REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.regions_mv1@mv1.world; /*
*/ BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => 'hr_repg', sname => 'hr', oname => 'countries_mv2', type => 'SNAPSHOT', min_communication => TRUE); END; / BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => 'hr_repg', sname => 'hr', oname => 'departments_mv2', type => 'SNAPSHOT', min_communication => TRUE); END; / BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => 'hr_repg', sname => 'hr', oname => 'employees_mv2', type => 'SNAPSHOT', min_communication => TRUE); END; / BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => 'hr_repg', sname => 'hr', oname => 'jobs_mv2', type => 'SNAPSHOT', min_communication => TRUE); END; / BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => 'hr_repg', sname => 'hr', oname => 'job_history_mv2', type => 'SNAPSHOT', min_communication => TRUE); END; / BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => 'hr_repg', sname => 'hr', oname => 'locations_mv2', type => 'SNAPSHOT', min_communication => TRUE); END; / BEGIN DBMS_REPCAT.CREATE_MVIEW_REPOBJECT ( gname => 'hr_repg', sname => 'hr', oname => 'regions_mv2', type => 'SNAPSHOT', min_communication => TRUE); END; / /*
All of the materialized view group objects that you add to the refresh group are refreshed at the same time to preserve referential integrity between related materialized views.
*/ BEGIN DBMS_REFRESH.ADD ( name => 'mviewadmin.hr_refg', list => 'hr.countries_mv2', lax => TRUE); END; / BEGIN DBMS_REFRESH.ADD ( name => 'mviewadmin.hr_refg', list => 'hr.departments_mv2', lax => TRUE); END; / BEGIN DBMS_REFRESH.ADD ( name => 'mviewadmin.hr_refg', list => 'hr.employees_mv2', lax => TRUE); END; / BEGIN DBMS_REFRESH.ADD ( name => 'mviewadmin.hr_refg', list => 'hr.jobs_mv2', lax => TRUE); END; / BEGIN DBMS_REFRESH.ADD ( name => 'mviewadmin.hr_refg', list => 'hr.job_history_mv2', lax => TRUE); END; / BEGIN DBMS_REFRESH.ADD ( name => 'mviewadmin.hr_refg', list => 'hr.locations_mv2', lax => TRUE); END; / BEGIN DBMS_REFRESH.ADD ( name => 'mviewadmin.hr_refg', list => 'hr.regions_mv2', lax => TRUE); END; / SET ECHO OFF SPOOL OFF /************************* END OF SCRIPT **********************************/
|
Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|