5 Creating a Materialized View Group

This chapter illustrates how to create a materialized view group at a remote materialized view replication site.

This chapter contains these topics:

Overview of Creating a Materialized View Group
Creating a Materialized View Group

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:

Overview of Creating a 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, "Configuring the Replication Sites" for information about setting up a materialized view site, and see Chapter 3, "Creating a Master Group" for information about creating a master group.

Figure 5-1 Creating a Materialized View Group

Description of "Figure 5-1 Creating a Materialized View Group"

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.example.com and mv2.example.com:

The materialized view group at mv1.example.com is based on the objects in the hr_repg master group at the orc1.example.com master site.
The materialized view group at mv2.example.com is based on the objects in the hr_repg materialized view group at the mv1.example.com 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 ******************************

Creating the Materialized View Group at mv1.example.com

Complete the following steps to create the hr_repg materialized view group at the mv1.example.com materialized view site. This materialized view group is based on the hr_repg master group at the orc1.example.com 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.example.com serves as the target master site for the mv1.example.com materialized view site. The required materialized view logs must be created at orc1.example.com.

*/

SET ECHO ON

SPOOL create_mv_group.out

CONNECT hr@orc1.example.com

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 CREATE MATERIALIZED VIEW LOG statement in the Oracle Database SQL Language Reference for detailed information about this SQL statement

Step 2 If they do not already exist, then create the replicated schema and its database link.

Before building your materialized view group, you must ensure 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@mv1.example.com

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;

ACCEPT password PROMPT 'Enter password for user: ' HIDE

CREATE USER hr IDENTIFIED BY &password;

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 ensure 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@mv1.example.com

CREATE DATABASE LINK orc1.example.com 
   CONNECT TO proxy_refresher IDENTIFIED BY &password;

/*

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@mv1.example.com

/*

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.example.com',
      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.example.com;

CREATE MATERIALIZED VIEW hr.departments_mv1 
  REFRESH FAST WITH PRIMARY KEY FOR UPDATE 
  AS SELECT * FROM hr.departments@orc1.example.com;

CREATE MATERIALIZED VIEW hr.employees_mv1 
  REFRESH FAST WITH PRIMARY KEY FOR UPDATE 
  AS SELECT * FROM hr.employees@orc1.example.com;

CREATE MATERIALIZED VIEW hr.jobs_mv1 
  REFRESH FAST WITH PRIMARY KEY FOR UPDATE 
  AS SELECT * FROM hr.jobs@orc1.example.com;

CREATE MATERIALIZED VIEW hr.job_history_mv1 
  REFRESH FAST WITH PRIMARY KEY FOR UPDATE 
  AS SELECT * FROM hr.job_history@orc1.example.com;

CREATE MATERIALIZED VIEW hr.locations_mv1 
  REFRESH FAST WITH PRIMARY KEY FOR UPDATE 
  AS SELECT * FROM hr.locations@orc1.example.com;

CREATE MATERIALIZED VIEW hr.regions_mv1 
  REFRESH FAST WITH PRIMARY KEY FOR UPDATE 
  AS SELECT * FROM hr.regions@orc1.example.com;

/*

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;
/

/*

Creating the Materialized View Group at mv2.example.com

Complete the following steps to create the hr_repg materialized view group at the mv2.example.com materialized view site. This materialized view group is based on the hr_repg materialized view group at the mv1.example.com 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.example.com serves as the target master internalized view site for the mv2.example.com materialized view site. The required materialized view logs must be created at mv1.example.com.

*/

CONNECT hr@mv1.example.com

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 CREATE MATERIALIZED VIEW LOG statement in the Oracle Database SQL Language Reference for detailed information about this SQL statement

Step 2 If they do not already exist, then create the replicated schema and its database link.

Before building your materialized view group, you must ensure 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@mv2.example.com
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;

ACCEPT password PROMPT 'Enter password for user: ' HIDE

CREATE USER hr IDENTIFIED BY &password;

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.

*/

CONNECT hr@mv2.example.com

CREATE DATABASE LINK mv1.example.com 
   CONNECT TO proxy_refresher IDENTIFIED BY &password;

/*

See Also:

Step 6 for more information about creating proxy master materialized view site users

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@mv2.example.com

/*

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.example.com',
      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.example.com;

CREATE MATERIALIZED VIEW hr.departments_mv2 
  REFRESH FAST WITH PRIMARY KEY FOR UPDATE 
  AS SELECT * FROM hr.departments_mv1@mv1.example.com;

CREATE MATERIALIZED VIEW hr.employees_mv2 
  REFRESH FAST WITH PRIMARY KEY FOR UPDATE 
  AS SELECT * FROM hr.employees_mv1@mv1.example.com;

CREATE MATERIALIZED VIEW hr.jobs_mv2 
  REFRESH FAST WITH PRIMARY KEY FOR UPDATE 
  AS SELECT * FROM hr.jobs_mv1@mv1.example.com;

CREATE MATERIALIZED VIEW hr.job_history_mv2 
  REFRESH FAST WITH PRIMARY KEY FOR UPDATE 
  AS SELECT * FROM hr.job_history_mv1@mv1.example.com;

CREATE MATERIALIZED VIEW hr.locations_mv2 
  REFRESH FAST WITH PRIMARY KEY FOR UPDATE 
  AS SELECT * FROM hr.locations_mv1@mv1.example.com;

CREATE MATERIALIZED VIEW hr.regions_mv2 
  REFRESH FAST WITH PRIMARY KEY FOR UPDATE 
  AS SELECT * FROM hr.regions_mv1@mv1.example.com;

/*

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 **********************************/