8 Managing a Materialized View Replication Environment

Refreshing Materialized Views

Refreshing a materialized view synchronizes the data in the materialized view's master(s) and the data in the materialized view. You can either refresh all of the materialized views in a refresh group at once, or you can refresh materialized views individually. If you have applications that depend on more than one materialized view at a materialized view site, then Oracle recommends using refresh groups so that the data is transactionally consistent in all of the materialized views used by the application.

The following example refreshes the hr_refg refresh group:

EXECUTE DBMS_REFRESH.REFRESH ('hr_refg');

The following example refreshes the hr.departments_mv materialized view:

BEGIN
   DBMS_MVIEW.REFRESH (
     list   =>  'hr.departments_mv', 
     method =>  '?');
END;
/

Changing a Materialized View Group's Master Site

To change the master site of a materialized view group at a level 1 materialized view site to another master site, call the SWITCH_MVIEW_MASTER procedure in the DBMS_REPCAT package, as shown in the following example:

BEGIN
   DBMS_REPCAT.SWITCH_MVIEW_MASTER (
      gname  => 'hr_repg',
      master => 'orc3.world');
END;
/

In this example, the master site for the hr_repg replication group is changed to the orc3.world master site. You must call this procedure at the materialized view site whose master site you want to change. The new database must be a master site in the replication environment. When you call this procedure, Oracle uses the new master to perform a full refresh of each materialized view in the local materialized view group. Ensure that you have set up the materialized view site to use the new master site before you run the SWITCH_MVIEW_MASTER procedure.

The entries in the SYS.SLOG$ table at the old master site for the switched materialized view are not removed. As a result, the materialized view log (MLOG$ table) of the switched updatable materialized view at the old master site has the potential to grow indefinitely, unless you purge it by calling DBMS_MVIEW.PURGE_LOG.

Dropping Materialized View Groups and Objects

You might need to drop replication activity at a materialized view site for a number of reasons. Perhaps the data requirements have changed or an employee has left the company. In any case, as a DBA you will need to drop the replication support for the target materialized view site.

This section contains the following sections:

• Dropping a Materialized View Group Created with a Deployment Template

• Dropping a Materialized View Group or Objects Created Manually

• Cleaning Up a Master Site or Master Materialized View Site

/************************* BEGINNING OF SCRIPT ******************************

*/

SET ECHO ON

SPOOL drop_mv_group_public.out

CONNECT hr/user-password@orc3.world

/*

*/

BEGIN
   DBMS_REPCAT_INSTANTIATE.DROP_SITE_INSTANTIATION( 
      refresh_template_name =>  'hr_refg_dt',
      site_name => 'mv4.world');
END;
/

/*

*/

CONNECT mviewadmin/user-password@mv4.world

/*

*/

BEGIN
   DBMS_REPCAT.DROP_MVIEW_REPGROUP (
      gname => 'hr_repg',
      drop_contents => TRUE);
END;
/

/*

*/

CONNECT hr/user-password@mv4.world

BEGIN
   DBMS_REFRESH.DESTROY (
       name => 'hr_refg');
END;
/

SET ECHO OFF

SPOOL OFF

/************************* END OF SCRIPT **********************************/

/************************* BEGINNING OF SCRIPT ******************************

*/

SET ECHO ON

SPOOL drop_mv_group_private.out

CONNECT repadmin/user-password@orc1.world

/*

*/

BEGIN
   DBMS_REPCAT_RGT.DROP_SITE_INSTANTIATION ( 
      refresh_template_name => 'hr_refg_dt',
      user_name             => 'hr',
      site_name             => 'mv4.world');
END;
/

/*

*/

CONNECT mviewadmin/user-password@mv4.world

/*

*/

BEGIN
   DBMS_REPCAT.DROP_MVIEW_REPGROUP (
      gname => 'hr_repg',
      drop_contents => TRUE,
      gowner => 'hr');
END;
/

/*

*/

CONNECT hr/user-password@mv4.world

BEGIN
   DBMS_REFRESH.DESTROY (
       name => 'hr_refg');
END;
/

SET ECHO OFF

SPOOL OFF

/************************* END OF SCRIPT **********************************/

CONNECT mviewadmin/user-password@mv1.world

BEGIN

DBMS_REPCAT.DROP_MVIEW_REPGROUP (
      gname => 'hr_repg',
      drop_contents => TRUE);
END;
/

CONNECT mviewadmin/user-password@mv1.world

BEGIN

DBMS_REPCAT.DROP_MVIEW_REPOBJECT (
      sname => 'hr', 
      oname => 'employees_mv1', 
      type => 'SNAPSHOT', 
      drop_objects => TRUE);
END;
/

/************************* BEGINNING OF SCRIPT ******************************

*/

SET ECHO ON

SPOOL cleanup_master1.out

CONNECT repadmin/user-password@orc1.world

/*

*/

BEGIN
   DBMS_REPCAT.UNREGISTER_MVIEW_REPGROUP (
      gname => 'hr_repg',
      mviewsite => 'mv1.world');
END;
/

/*

*/

BEGIN
   DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
      mviewowner => 'hr',
      mviewname => 'countries_mv1', 
      mviewsite => 'mv1.world');
END;
/

BEGIN
   DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
      mviewowner => 'hr',
      mviewname => 'departments_mv1', 
      mviewsite => 'mv1.world');
END;
/

BEGIN
   DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
      mviewowner => 'hr',
      mviewname => 'employees_mv1', 
      mviewsite => 'mv1.world');
END;
/

BEGIN
   DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
      mviewowner => 'hr',
      mviewname => 'jobs_mv1', 
      mviewsite => 'mv1.world');
END;
/

BEGIN
   DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
      mviewowner => 'hr',
      mviewname => 'job_history_mv1', 
      mviewsite => 'mv1.world');
END;
/

BEGIN
   DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
      mviewowner => 'hr',
      mviewname => 'locations_mv1', 
      mviewsite => 'mv1.world');
END;
/

BEGIN
   DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
      mviewowner => 'hr',
      mviewname => 'regions_mv1', 
      mviewsite => 'mv1.world');
END;
/

SET ECHO OFF

SPOOL OFF

/************************* END OF SCRIPT **********************************/

/************************* BEGINNING OF SCRIPT ******************************

*/

SET ECHO ON

SPOOL cleanup_master2.out

CONNECT repadmin/user-password@orc1.world

/*

*/

BEGIN
   DBMS_MVIEW.UNREGISTER_MVIEW (
      mviewowner => 'hr',
      mviewname => 'employees_mv1',
      mviewsite => 'mv1.world');
END;
/

/*

*/

BEGIN
   DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
      mviewowner => 'hr',
      mviewname => 'employees_mv1', 
      mviewsite => 'mv1.world');
END;
/

SET ECHO OFF

SPOOL OFF

/************************* END OF SCRIPT **********************************/

Managing Materialized View Logs

The following sections explain how to manage materialized view logs:

• Altering Materialized View Logs

• Managing Materialized View Log Space

• Reorganizing Master Tables that Have Materialized View Logs

• Dropping a Materialized View Log

ALTER MATERIALIZED VIEW LOG ON hr.employees
  PCTFREE 25
  PCTUSED 40;

ALTER MATERIALIZED VIEW LOG ON hr.employees
  ADD (department_id);

BEGIN
   DBMS_MVIEW.PURGE_LOG (
      master => 'hr.employees',
      num    => 1,
      flag   => 'DELETE');
END;
/

LOCK TABLE hr.employees IN EXCLUSIVE MODE;

CREATE TABLE hr.templog AS SELECT * FROM hr.MLOG$_employees;

TRUNCATE TABLE hr.MLOG$_employees;

INSERT INTO hr.MLOG$_employees SELECT * FROM hr.templog;

DROP TABLE hr.templog;

ROLLBACK;

TRUNCATE TABLE hr.employees PRESERVE MATERIALIZED VIEW LOG;
TRUNCATE TABLE hr.employees;

TRUNCATE TABLE hr.employees PURGE MATERIALIZED VIEW LOG;

DROP MATERIALIZED VIEW LOG ON hr.employees;

Performing an Offline Instantiation of a Materialized View Site Using Export/Import

Adding a new materialized view site to your replication environment can cause network traffic. The network traffic is caused by propagating the entire contents of tables or materialized views through the network to the new materialized view site.

To minimize such network traffic, you can add a new materialized view site using offline instantiation procedure. With offline instantiation, you can create a new materialized view group at a materialized view site. Offline instantiation uses of Oracle's Export and Import utilities, which allow you to create an export file and transfer the data to the new site through a storage medium, such as CD-ROM, tape, and so on. Offline instantiation is especially useful for materialized views, because the target computer could be a laptop using a modem connection.

The following script performs an offline instantiation for a new materialized view group at a new materialized view site. The materialized view group is based on an existing master group at a master site. Meet the following requirements to complete these actions:

Executed As:

• Replication Administrator at Master Site

• Materialized View Administrator at New Materialized View Site

Executed At:

• Master Site for Target Materialized View Site

• New Materialized View Site

Replication Status: Normal

Materialized View Site:

• Set up materialized view site. In this example, the materialized view site is mview.world and the master site is orc1.world.

• Ensure that the appropriate schema has been created before the offline instantiation of your materialized view site.

• Create proxy users at the master site if they do not exist.

Complete the following steps to set up a materialized view site named mview.world.

/************************ BEGINNING OF SCRIPT *********************************

Step 1   Connect to the master site as the replication administrator.

*/

SET ECHO ON

SPOOL offline.out

CONNECT repadmin/user-password@orc1.world

/*

Step 2   Create the necessary materialized view logs, if they do not exist.

If materialized view logs do not already exist for the relevant master tables, then create them at the master site.

*/

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;

/*

Step 3   Create a temporary schema at the master site for the materialized views.

To prepare materialized views for export, you must create the schema that contains the replicated objects.

In this example, create a temporary schema temp_schema.

*/

CONNECT system/user-password@orc1.world

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

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

CREATE USER temp_schema IDENTIFIED BY user-password;

ALTER USER temp_schema DEFAULT TABLESPACE offline_mview
              QUOTA UNLIMITED ON offline_mview;

ALTER USER temp_schema TEMPORARY TABLESPACE offline_temp_mview;

GRANT ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE,
  CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW, CREATE INDEXTYPE, 
  CREATE OPERATOR, CREATE PROCEDURE, CREATE TRIGGER, CREATE TYPE, 
  CREATE MATERIALIZED VIEW, SELECT ANY TABLE
TO temp_schema;

CONNECT temp_schema/user-password@orc1.world;

/*

Step 4   Create temporary materialized views at the master site in the separate schema you created in Step 3.

These materialized views contain the data that you transfer to your new materialized view site using the Export utility.

*/

CREATE MATERIALIZED VIEW temp_schema.countries
      REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT *
      FROM hr.countries@orc1.world;

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

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

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

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

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

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

/*

Step 5   Create a directory object at each database.

Each database involved in this operation must have a directory object to hold the Data Pump dump file, and the user who will perform the export or import must have READ and WRITE privileges on this directory object. In this example, a Data Pump export is performed at the master site, and a Data Pump import is performed at the materialized view site.

While connected in SQL*Plus to a database as an administrative user who can create directory objects using the SQL statement CREATE DIRECTORY, create a directory object to hold the Data Pump dump file and log files. For example:

*/

CONNECT system/user-password@orc1.world

CREATE DIRECTORY DPUMP_DIR AS '/usr/dpump_dir';

CONNECT system/user-password@mview.world

CREATE DIRECTORY DPUMP_DIR AS '/usr/dpump_dir';

/*

Ensure that you complete these actions at both databases involved in the operation. In this example, SYSTEM user creates the directory objects and performs all exports and imports. If a user who does not own the directory object will perform the export or import, then grant the user READ and WRITE privileges on the directory object.

Step 6   Perform a schema-level export of the schema containing the materialized views.

On a command line, perform the export that will contain all data and metadata for the materialized views. This example connects as the SYSTEM user. The following is an example Data Pump export command:

expdp system/user-password SCHEMAS=temp_schema DIRECTORY=DPUMP_DIR 
DUMPFILE=temp_schema.dmp

*/

PAUSE Press <RETURN> to continue when the export is complete.

/*

Step 7   Connect to the new materialized view site as SYSTEM user.

*/

CONNECT system/user-password@mview.world

/*

Step 8   Drop the hr User

This example creates the materialized views in the hr schema at the materialized view site. This schema is created when Oracle is installed. This step drops the schema, but the schema will be re-created and populated with materialized views later in this example.

*/

DROP USER hr CASCADE;

/*

Step 9   Create necessary schema and database link at the materialized view site, if they do not exist.

Before you perform the offline instantiation of your materialized views, create the schema that will contain the materialized views at the new materialized view site and the database link from the materialized view site to the master site. The materialized views must be in the same schema that contains the master objects at the master site. If the schema exists, then grant the necessary privileges and create the database link.

*/

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

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

CREATE USER hr IDENTIFIED BY user-password;

ALTER USER hr DEFAULT TABLESPACE demo_mview
              QUOTA UNLIMITED ON demo_mview;

ALTER USER hr TEMPORARY TABLESPACE temp_mview;

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/user-password@mview.world

CREATE DATABASE LINK orc1.world CONNECT TO hr IDENTIFIED by user-password;

/*

Step 10   Connect to the new materialized view site as the materialized view administrator.

*/

CONNECT mviewadmin/user-password@mview.world

/*

Step 11   Create an empty materialized view group.

Run the DBMS_REPCAT.CREATE_MVIEW_REPGROUP procedure at the new materialized view site to create an empty materialized view group into which you will add your materialized views.

*/

BEGIN
   DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
      gname            => 'hr_repg',
      master           => 'orc1.world',
      propagation_mode => 'ASYNCHRONOUS');
END;
/

/*

Step 12   Create an empty 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 13   In a separate terminal window, transfer the export dump file to the new materialized view site.

Using the DBMS_FILE_TRANSFER package, FTP or some other method, transfer the export dump file to the new materialized view site.

*/ 

PAUSE Press <RETURN> to continue after transferring the dump file. 

/*

Step 14   In a separate terminal window, import the materialized views to the owner at the new materialized view site.

On a command line, perform the import of the file that you exported in Step 5. This example connects as the SYSTEM user.

If you use Data Pump, then ensure that you import your data using the REMAP_SCHEMA parameter to import the data from the temporary user you created at the master site to the owner of the materialized views at the materialized view site. In this example, the temporary user at the master site is temp_schema and the materialized view owner at the materialized view site is hr.

Also, if you use Data Pump, then you can use the REMAP_TABLESPACE parameter if the tablespace is different at the master site and the materialized view site. In this example, the tablespace at the master site is offline_mview (created in Step 3) and the tablespace at the materialized view site is demo_mview (created in Step 9).

The following is an example import command:

impdp system/user-password DIRECTORY=DPUMP_DIR DUMPFILE=temp_schema.dmp 
REMAP_SCHEMA=temp_schema:hr REMAP_TABLESPACE=offline_mview:demo_mview

Only users with the DBA role or the IMP_FULL_DATABASE role can import using the REMAP_SCHEMA parameter.

*/

PAUSE Press <RETURN> to continue when the import is complete.

/*

Step 15   Add materialized views to the materialized view group.

Execute the DBMS_REPCAT.CREATE_MVIEW_REPOBJECT procedure to add the materialized views to the materialized view group you created in Step 9.

*/

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

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

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

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

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

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

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

/*

Step 16   Add the materialized views 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. Execute the DBMS_REFRESH.ADD procedure to add the materialized views to the refresh group you created in Step 12.

*/

BEGIN
   DBMS_REFRESH.ADD (
      name => 'mviewadmin.hr_refg',
      list => 'hr.countries',
      lax => TRUE);
END;
/

BEGIN
   DBMS_REFRESH.ADD (
      name => 'mviewadmin.hr_refg',
      list => 'hr.departments',
      lax => TRUE);
END;
/

BEGIN
   DBMS_REFRESH.ADD (
      name => 'mviewadmin.hr_refg',
      list => 'hr.employees',
      lax => TRUE);
END;
/

BEGIN
   DBMS_REFRESH.ADD (
      name => 'mviewadmin.hr_refg',
      list => 'hr.jobs',
      lax => TRUE);
END;
/

BEGIN
   DBMS_REFRESH.ADD (
      name => 'mviewadmin.hr_refg',
      list => 'hr.job_history',
      lax => TRUE);
END;
/

BEGIN
   DBMS_REFRESH.ADD (
      name => 'mviewadmin.hr_refg',
      list => 'hr.locations',
      lax => TRUE);
END;
/

BEGIN
   DBMS_REFRESH.ADD (
      name => 'mviewadmin.hr_refg',
      list => 'hr.regions',
      lax => TRUE);
END;
/

/*

Step 17   Refresh materialized views to register them at master site.

In addition to retrieving the latest changes from the master tables, refreshing the materialized views at the new materialized view site registers the offline instantiated materialized views at the target master site.

*/

EXECUTE DBMS_REFRESH.REFRESH ('hr_refg');

/*

Step 18   Connect to the master site as SYSTEM user.

*/

CONNECT system/user-password@orc1.world

/*

Step 19   Drop the temporary schema to delete the temporary materialized views you created in Step 4 at the master site.

*/

DROP USER temp_schema CASCADE;

SET ECHO OFF

SPOOL OFF

/************************* END OF SCRIPT **********************************/

Using a Group Owner for a Materialized View Group

Specifying a group owner when you define a new materialized view group and its related objects enables you to create multiple materialized view groups based on the same replication group at a single materialized view site. Also, specifying group owners enables you to create multiple materialized view groups that are based on the same replication group at a master site or master materialized view site. You accomplish this by creating the materialized view groups under different schemas at the materialized view site. This example uses the schemas bob and jane as group owners and assumes that these schemas exist at the materialized view site.

Executed As:

• Materialized View Administrator at New Materialized View Site

Executed At:

• Materialized View Site

Replication Status: Normal

Materialized View Site:

• Set up materialized view site. In this example, the materialized view site is mv1.world and the master site is orc1.world.

• Create proxy users at the master site if they do not exist.

• Create materialized view logs for the tables in the hr schema at the master site if they do not exist.

Complete the following steps to use a group owner.

/************************* BEGINNING OF SCRIPT ******************************

Step 1   Create a database link from the hr schema to the master site

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.

*/

CONNECT system/user-password@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 user-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.

*/

SET ECHO ON

SPOOL mv_group_owner.out

CONNECT hr/user-password@mv1.world

CREATE DATABASE LINK orc1.world 
   CONNECT TO proxy_refresher IDENTIFIED BY user-password;

/*

Step 2   Connect to the materialized view site as the materialized view administrator.

*/

CONNECT mviewadmin/user-password@mv1.world

/*

Step 3   Create materialized view group with group owner (gowner) bob using the CREATE_MVIEW_REPGROUP procedure.

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 site or master materialized view site. The gowner parameter enables you to specify an additional identifier that lets you create multiple materialized view groups based on the same replication group at the same materialized view site.

In this example, materialized view groups are created for the group owners bob and jane, and these two materialized view groups are based on the same replication group.

*/

BEGIN
   DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
      gname => 'hr_repg',
      master => 'orc1.world',
      propagation_mode => 'ASYNCHRONOUS',
      gowner => 'bob');
END;
/

BEGIN
   DBMS_REPCAT.CREATE_MVIEW_REPGROUP (
      gname => 'hr_repg',
      master => 'orc1.world',
      propagation_mode => 'ASYNCHRONOUS',
      gowner => 'jane');
END;
/

/*

Step 4   Create the materialized views owned by bob.

The gowner value used when creating your materialized view objects must match the gowner value specified when you created the materialized view group in the previous procedures. After creating the materialized view groups, you can create materialized views based on the same master in the hr_repg materialized view group owned by bob and jane. This example assumes that these users exist.

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_bob 
  REFRESH FAST WITH PRIMARY KEY FOR UPDATE 
  AS SELECT * FROM hr.countries@orc1.world;

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

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

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

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

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

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

/*

Step 5   Create the materialized views owned by jane.

*/

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

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

/*

Step 6   Add the materialized views owned by bob to the materialized view group.

*/

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

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

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

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

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

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

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

/*

Step 7   Add the materialized views owned by jane to the materialized view group.

*/

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

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

SET ECHO OFF

SPOOL OFF

/*

Step 8   Add your materialized views to a refresh group.

/************************* END OF SCRIPT **********************************/