关于物化视图的搭建

Figure 2-3 Setting Up Materialized View Sites

Setting Up mv1.world

Complete the following steps to set up the mv1.world master materialized view site. mv1.world is a master materialized view site because mv2.world will be based on it. Before you set up the materialized sites, configure your network and Oracle Net so that all mv1.world can communicate with orc1.world and mv2.world can communicate with mv1.world.

---

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

Step 1 Connect as SYSTEM at materialized view site at mv1.world.

You must connect as SYSTEM to the database that you want to set up as a materialized view site.

*/

SET ECHO ON

SPOOL setup_mvs.out

CONNECT SYSTEM/[e-mail]

/*

Step 2 Create materialized view site users at mv1.world.

Several users must be created at the materialized view site. These users are:

• Materialized view administrator
• Propagator
• Refresher
• Receiver (if the site will serve as a master materialized view site for other materialized views, as mv1.world is)

Complete the following tasks to create these users.

Create the materialized view administrator.

The materialized view administrator is responsible for creating and managing the materialized view site. Execute the GRANT_ADMIN_ANY_SCHEMA procedure to grant the materialized view administrator the appropriate privileges.

*/

create user MVIEWADMIN identified by MVIEWADMIN;

BEGIN

      username => 'mviewadmin');
END;
/

GRANT COMMENT ANY TABLE TO mviewadmin;

GRANT LOCK ANY TABLE TO mviewadmin;

/*

If you want your mviewadmin to be able to connect to the Replication Management tool, then grant SELECT ANY DICTIONARY to mviewadmin:

*/

GRANT SELECT ANY DICTIONARY TO mviewadmin;

/*

Create the propagator.

The propagator is responsible for propagating the deferred transaction queue to the target master site.

*/

CREATE USER propagator IDENTIFIED BY propagator;

BEGIN

      username => 'propagator');
END;
/

/*

Create the refresher.

The refresher is responsible for "pulling" changes made to the replicated tables at the target master site to the materialized view site. This user refreshes one or more materialized views. If you want the mviewadmin user to be the refresher, then this step is not required.

*/

CREATE USER refresher IDENTIFIED BY refresher;

GRANT CREATE SESSION TO refresher;

GRANT ALTER ANY MATERIALIZED VIEW TO refresher;

/*

Register the receiver.

The receiver receives the propagated deferred transactions sent by the propagator from materialized view sites. The receiver is necessary only if the site will function as a master materialized view site for other materialized view sites.

*/

BEGIN

      username => 'mviewadmin',
      privilege_type => 'receiver',
      list_of_gnames => NULL);
END;
/

/*

Step 3 Create database links to the master site.

Create a public database link.

*/

CONNECT SYSTEM/[e-mail]

CREATE PUBLIC DATABASE LINK orc1.world USING 'orc1.world';

/*

Create the materialized view administrator database link.

You need to create a database link from the materialized view administrator at the materialized view site to the proxy materialized view administrator at the master site.

*/

CONNECT mviewadmin/[e-mail];

CREATE DATABASE LINK orc1.world 
  CONNECT TO proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

/*

Create the propagator/receiver database link.

You need to create a database link from the propagator at the materialized view site to the receiver at the master site. The receiver was defined when you created the master site.

*/

CONNECT propagator/[e-mail]

CREATE DATABASE LINK orc1.world 
  CONNECT TO repadmin IDENTIFIED BY repadmin;

/*

See Also:

Step

Step 4 Schedule purge at the mv1.world materialized view site.

In order to keep the size of the deferred transaction queue in check, you should purge successfully completed deferred transactions. The SCHEDULE_PURGE procedure automates the purge process for you. If your materialized view site only contains "read-only" materialized views, then you do not need to execute this procedure.

*/

CONNECT mviewadmin/[e-mail]

BEGIN

   next_date => SYSDATE,
   interval => 'SYSDATE + 1/24',
   delay_seconds => 0,
   rollback_segment => '');
END;
/

/*

Step 5 Schedule push at the mv1.world materialized view site (optional).

If the materialized view site has a constant connection to its master site, then you optionally can schedule push at the mv1.world materialized view site. If the materialized view site is disconnected from its master site for extended periods of time, then it is typically better not to schedule push and refresh on demand, which pushes changes to the master site.

The SCHEDULE_PUSH procedure schedules when the deferred transaction queue should be propagated to the target master site.

*/

CONNECT mviewadmin/[e-mail]

BEGIN

      destination => 'orc1.world',
      interval => 'SYSDATE + 1/24',
      next_date => SYSDATE,
      stop_on_error => FALSE,
      delay_seconds => 0,
      parallelism => 0);
END;
/

/*

Step 6 Create proxy users at the mv1.world materialized view site.

Create the proxy materialized view administrator.

The proxy materialized view administrator performs tasks at the target master materialized view site on behalf of the materialized view administrator at the materialized view sites based on this materialized view site. This user is not required if the site will not function as a master materialized view site for other materialized view sites.

*/

CONNECT SYSTEM/[e-mail]

CREATE USER proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

BEGIN

      username => 'proxy_mviewadmin',
      privilege_type => 'proxy_snapadmin',
      list_of_gnames => NULL);
END;
/

-- Place GRANT SELECT_CATALOG_ROLE statement here if necessary.

/*

If you want your materialized view administrator at materialized view sites based on this materialized view site to be able to perform administrative operations using the Replication Management tool, then grant SELECT_CATALOG_ROLE to proxy_mviewadmin:

GRANT SELECT_CATALOG_ROLE TO proxy_mviewadmin;

Granting this privilege to the proxy_mviewadmin is not required if you do not plan to use the Replication Management tool. However, if you plan to use the Replication Management tool, then move the GRANT statement to the line directly after the previous REGISTER_USER_REPGROUP statement.

Create the proxy refresher.

The proxy refresher performs tasks at the master materialized view site on behalf of the refresher at the materialized view sites based on this materialized view site. This user is not required if the site will not function as a master materialized view site for other materialized view sites.

*/

CREATE USER proxy_refresher IDENTIFIED BY proxy_refresher;

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

/*

See Also:

"Security Setup for Materialized View Replication"

Setting Up mv2.world

Complete the following steps to set up the mv2.world materialized view site. mv2.world is part of a multitier materialized view configuration because it is based on mv1.world, another materialized view.

Step 1 Connect as SYSTEM at level 2 materialized view site mv2.world.

You must connect as SYSTEM to the database that you want to set up as a level 2 materialized view site. This site, mv2.world, will be a materialized view site that is based on mv1.world.

*/

CONNECT SYSTEM/[e-mail]

/*

Step 2 Create level 2 materialized view site users at mv2.world.

Several users must be created at the level 2 materialized view site. These users are:

• Materialized view administrator
• Propagator
• Refresher

Complete the following tasks to create these users.

Create the materialized view administrator.

The materialized view administrator is responsible for creating and managing the level 2 materialized view site. Execute the GRANT_ADMIN_ANY_SCHEMA procedure to grant the materialized view administrator the appropriate privileges.

*/

create user MVIEWADMIN identified by MVIEWADMIN;

BEGIN

      username => 'mviewadmin');
END;
/

/*

If you want your mviewadmin to be able to connect to the Replication Management tool, then grant SELECT ANY DICTIONARY to mviewadmin:

*/

GRANT SELECT ANY DICTIONARY TO mviewadmin;

/*

Create the propagator.

The propagator is responsible for propagating the deferred transaction queue to the target master materialized view site.

*/

CREATE USER propagator IDENTIFIED BY propagator;

BEGIN

      username => 'propagator');
END;
/

/*

Create the refresher.

The refresher is responsible for "pulling" changes made to the replicated materialized views at the target master materialized view site to the level 2 materialized view site.

*/

CREATE USER refresher IDENTIFIED BY refresher;

GRANT CREATE SESSION TO refresher;
GRANT ALTER ANY MATERIALIZED VIEW TO refresher;

/*

Step 3 Create database links to master materialized view site.

Create a public database link.

*/

CONNECT SYSTEM/[e-mail]

CREATE PUBLIC DATABASE LINK mv1.world USING 'mv1.world';

/*

Create materialized view administrator database link.

You need to create a database link from the materialized view administrator at the level 2 materialized view site to the proxy materialized view administrator at the master materialized view site.

*/

CONNECT mviewadmin/[e-mail];

CREATE DATABASE LINK mv1.world 
  CONNECT TO proxy_mviewadmin IDENTIFIED BY proxy_mviewadmin;

/*

Create a propagator/receiver database link.

You need to create a database link from the propagator at the level 2 materialized view site to the receiver at the master materialized view site. The receiver was defined when you created the master materialized view site.

*/

CONNECT propagator/[e-mail]

CREATE DATABASE LINK mv1.world 
  CONNECT TO mviewadmin IDENTIFIED BY mviewadmin;

/*

Step 4 Schedule purge at level 2 materialized view site at mv2.world.

In order to keep the size of the deferred transaction queue in check, you should purge successfully completed deferred transactions. The SCHEDULE_PURGE procedure automates the purge process for you. If your level 2 materialized view site only contains "read-only" materialized views, then you do not need to execute this procedure.

*/

CONNECT mviewadmin/[e-mail]

BEGIN

     next_date => SYSDATE,
     interval => 'SYSDATE + 1/24',
     delay_seconds => 0,
     rollback_segment => '');
END;
/

/*

Step 5 Schedule push at the mv2.world materialized view site (optional).

If the materialized view site has a constant connection to its master materialized view site, then you optionally can schedule push at the mv2.world materialized view site. If the materialized view site is disconnected from its master materialized view site for extended periods of time, then it is typically better not to schedule push and refresh on demand, which pushes changes to the master materialized view site.

The SCHEDULE_PUSH procedure schedules when the deferred transaction queue should be propagated to the target master materialized view site.

*/

CONNECT mviewadmin/[e-mail]

BEGIN

      destination => 'mv1.world',
      interval => 'SYSDATE + 1/24',
      next_date => SYSDATE,
      stop_on_error => FALSE,
      delay_seconds => 0,
      parallelism => 0);
END;
/

SET ECHO OFF

SPOOL OFF

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