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.
See Also:
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.Figure 5-1 Creating a Materialized View 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
:
The materialized view group at mv1.world
is based on the objects in the hr_repg
master group at the orc1.world
master site.
The materialized view group at 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.
Note:
If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment./************************* 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.
Step 1 Create materialized view logs at the 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:
TheCREATE
MATERIALIZED
VIEW
LOG
statement in the Oracle Database SQL Reference for detailed information about this SQL statementStep 2 If they do not already exist, then create the replicated schema and its database link.
Before building your materialized view group, you must make sure that the replicated schema exists at the remote materialized view site and that the necessary database links have been created.
In this example, if the hr
schema does not exist, then create the schema. If the hr
schema already exists at the materialized view site, then grant any necessary privileges and go to the next task in this step.
*/ 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; /*
If it does not already exist, then create the database link for the replicated schema.
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; /*
Step 3 Create the materialized view group.
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; / /*
Step 4 Create the refresh group.
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; / /*
Step 5 Add objects to the materialized view group.
Create the materialized views based on the master tables.
Whenever you create a materialized view, always specify the schema name of the table owner in the query for the materialized view. In the following examples, 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; /*
Add the objects to the materialized view group.
*/ 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; / /*
Step 6 Add objects to the refresh group.
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.
Step 1 Create materialized view logs at the master 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:
TheCREATE
MATERIALIZED
VIEW
LOG
statement in the Oracle Database SQL Reference for detailed information about this SQL statementStep 2 If they do not already exist, then create the replicated schema and its database link.
Before building your materialized view group, you must make sure that the replicated schema exists at the remote materialized view site and that the necessary database links have been created.
For this example, if the hr schema does not exist, then create the schema. If the hr schema already exists at the materialized view site, then go to the next task in this step.
*/ 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; /*
If it does not already exist, then create the database link for the replicated schema.
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 usersStep 3 Create the materialized view group.
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; / /*
Step 4 Create the refresh group.
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; / /*
Step 5 Add objects to the materialized view group.
Create the materialized views based on the master materialized views.
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 following examples, 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; /*
Add the materialized views to the materialized view group.
*/ 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; / /*
Step 6 Add objects to the refresh group.
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 **********************************/