Configuring Materialized View Sites

Before you configure materialized views, you must configure the databases that will function as materialized view sites in your replication environment. Completing the steps in this topic will configure the necessary users and database links required by the materialized view environment. In addition, if you plan to configure updatable materialized views, then these steps also configure scheduled links and scheduled purges for changes made to updatable materialized views.

To configure materialized view sites with Enterprise Manager: 

1. Complete the actions described in "Preparing for Materialized View Replication".

2. In Oracle Enterprise Manager, log in to the materialized view site as an administrative user, such as SYSTEM.

3. Go to the Database Home page.

4. Click Data Movement to open the Data Movement subpage.

5. Click Setup in the Advanced Replication section.

6. On the Advanced Replication: Setup page, expand Updateable Materialized View Replication.

7. Select Configure Materialized View Sites for Replication.

8. Click Continue to open the Configure Materialized View Sites for Replication Wizard.

   Complete the pages in the wizard to configure the materialized view sites. On the Create Users page, enter an appropriate password for the materialized view administrator. See Requirements for Creating Passwords for information about choosing passwords. For more information about using the wizard, click Help for each page of the wizard.

   Tip: On the Create Schemas page of the wizard, make sure you select the schemas that contain the database objects that you want to replicate.

   When you finish the wizard, an Enterprise Manager job is scheduled to configure the materialized view sites. After the job runs successfully, the materialized view sites are configured, and each one has a materialized view administrator. By default, the user name of the materialized view administrator is mvadmin. You specified the password for this user when you completed the wizard. Also, each materialized view site contains each schema that you specified on the Create Schemas page of the wizard.

9. At each materialized view site, create the required database links for the replicated schemas.

   To create materialized views in a schema at the materialized view site, the schema must be able to connect to the corresponding schema at the master site through a database link.

   To create the database links:

   1. On a command line, open SQL*Plus and connect to the materialized view database as the owner of the replicated schema.

      For example, if you plan to create materialized views in the hr schema at the ii2.example.com database, then enter the following:

      sqlplus hr@ii2.example.com
      Enter password: password
      

      See Starting SQL*Plus and Connecting to the Database for more information about starting SQL*Plus.

   2. Create a database link that connects to the corresponding replicated schema at the master database.

      For example, to create a database link that connects to the hr schema at the ii1.example.com database, enter the following:

      CREATE DATABASE LINK ii1.example.com CONNECT TO hr 
         IDENTIFIED BY password USING 'ii1.example.com';
      

   3. Repeat Steps a through b for each replicated schema at the materialized view database.

   4. Repeat Steps a through c for each materialized view database.