Materialized view replication provides the flexibility to build data sets to meet the needs of your users and front-end applications, while still meeting the requirements of your security configuration. This chapter describes how to manage materialized view sites with the replication management API.
This chapter contains these topics:
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 multiple materialized views 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; /
Note:
Do not use theDBMS_MVIEW.REFRESH_ALL_MVIEWS or the DBMS_MVIEW.REFRESH_DEPENDENT procedure to refresh materialized views used in a replication environment. Instead, use the DBMS_REFRESH.REFRESH or the DBMS_MVIEW.REFRESH procedure to refresh materialized views in a replication environment.See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about theDBMS_MVIEW packageTo 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.example.com'); END; /
In this example, the master site for the hr_repg replication group is changed to the orc3.example.com 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.
Note:
You cannot switch the master of materialized views that are based on other materialized views (level 2 and greater materialized views). Such a materialized view must be dropped and re-created to base it on a different master.See Also:
"Setting Up Materialized View Sites"You might need to drop replication activity at a materialized view site for several reasons. Perhaps the data requirements have changed or an employee has left the company.
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
If a materialized view group was created with a deployment template, then, before you drop the materialized view group at the remote materialized view site, you must execute the DROP_SITE_INSTANTIATION procedure at the target master site of the materialized view group. In addition to removing the metadata relating to the materialized view group, this procedure also removes the related deployment template data regarding this site.
The DROP_SITE_INSTANTIATION procedure has a public and a private version. The public version allows the owner of the materialized view group to drop the materialized view site, while the private version allows the replication administrator to drop a materialized view site on behalf of the materialized view group owner.
Meet the following requirements to complete these actions:
Executed As:
Materialized View Group Owner at Master Site
Materialized View Administrator at Materialized View Site
Executed At:
Master Site for Target Materialized View Site
Materialized View Site
Replication Status: Normal
Complete the following steps to drop a materialized view group created with a deployment template.
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 ******************************
*/
SET ECHO ON SPOOL drop_mv_group_public.out CONNECT hr@orc3.example.com /*
*/ BEGIN DBMS_REPCAT_INSTANTIATE.DROP_SITE_INSTANTIATION( refresh_template_name => 'hr_refg_dt', site_name => 'mv4.example.com'); END; / /*
*/
CONNECT mviewadmin@mv4.example.com /*
If you are not able to connect to the remote materialized view site, then the target materialized view group cannot refresh, but the existing data still remains at the materialized view site.
*/
BEGIN DBMS_REPCAT.DROP_MVIEW_REPGROUP ( gname => 'hr_repg', drop_contents => TRUE); END; / /*
To physically remove the contents of the materialized view group from the materialized view database, specify TRUE for the drop_contents parameter.
Connect as the refresh group owner and remove the refresh group.
*/ CONNECT hr@mv4.example.com BEGIN DBMS_REFRESH.DESTROY ( name => 'hr_refg'); END; / SET ECHO OFF SPOOL OFF /************************* END OF SCRIPT **********************************/
The following steps are to be performed by the replication administrator on behalf of the materialized view group owner. Meet the following requirements to complete these actions:
Executed As:
Replication Administrator at Master Site
Materialized View Administrator at Materialized View Site
Executed At:
Master Site for Target Materialized View Site
Materialized View Site
Replication Status: Normal
Complete the following steps to drop a materialized view group created with a deployment template.
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 ******************************
*/
SET ECHO ON SPOOL drop_mv_group_private.out CONNECT repadmin@orc1.example.com /*
*/ BEGIN DBMS_REPCAT_RGT.DROP_SITE_INSTANTIATION ( refresh_template_name => 'hr_refg_dt', user_name => 'hr', site_name => 'mv4.example.com'); END; / /*
*/
CONNECT mviewadmin@mv4.example.com /*
If you are unable to connect to the remote materialized view site, then the target materialized view group cannot refresh, but the existing data still remains at the materialized view site.
*/
BEGIN DBMS_REPCAT.DROP_MVIEW_REPGROUP ( gname => 'hr_repg', drop_contents => TRUE, gowner => 'hr'); END; / /*
To physically remove the contents of the materialized view group from the materialized view database, specify TRUE for the drop_contents parameter.
Connect as the refresh group owner and remove the refresh group.
*/ CONNECT hr@mv4.example.com BEGIN DBMS_REFRESH.DESTROY ( name => 'hr_refg'); END; / SET ECHO OFF SPOOL OFF /************************* END OF SCRIPT **********************************/
The most secure method of removing replication support for a materialized view site is to physically drop the replicated objects or groups at the materialized view site. The following two sections describe how to drop these objects and groups while connected to the materialized view group.
Ideally, these procedures should be executed while the materialized view is connected to its target master site or master materialized view site. A connection ensures that any related metadata at the master site or master materialized view site is removed. If a connection to the master site or master materialized view site is not possible, then be sure to complete the procedure described in "Cleaning Up a Master Site or Master Materialized View Site" to manually remove the related metadata.
When it becomes necessary to remove a materialized view group from a materialized view site, use the DROP_MVIEW_REPGROUP procedure to drop a materialized view group. When you execute this procedure and are connected to the target master site or master materialized view site, the metadata for the target materialized view group at the master site or master materialized view site is removed. If you cannot connect, then see "Cleaning Up a Master Site or Master Materialized View Site" for more information.
Meet the following requirements to complete these actions:
Executed As: Materialized View Administrator
Executed At: Remote Materialized View Site
Replication Status: N/A
Complete the following steps to drop a materialized view group at a materialized view site:
CONNECT mviewadmin@mv1.example.com
Enter password: password
BEGIN
DBMS_REPCAT.DROP_MVIEW_REPGROUP ( gname => 'hr_repg', drop_contents => TRUE); END; /
To physically remove the contents of the materialized view group from the materialized view database, specify TRUE for the drop_contents parameter.
When it becomes necessary to remove an individual materialized view from a materialized view site, use the DROP_MVIEW_REPOBJECT procedure API to drop a materialized view. When you execute this procedure and are connected to the target master site or master materialized view site, the metadata for the target materialized view at the master site or master materialized view site is removed. If you cannot connect, then see "Cleaning Up a Master Site or Master Materialized View Site" for more information.
Meet the following requirements to complete these actions:
Executed As: Materialized View Administrator
Executed At: Remote Materialized View Site
Replication Status: N/A
Complete the following steps to drop an individual materialized view at a materialized view site.
CONNECT mviewadmin@mv1.example.com
Enter password: password
BEGIN
DBMS_REPCAT.DROP_MVIEW_REPOBJECT ( sname => 'hr', oname => 'employees_mv1', type => 'SNAPSHOT', drop_objects => TRUE); END; /
To physically remove the contents of the materialized view from the materialized view database, specify TRUE for the drop_contents parameter.
If you are unable to drop a materialized view group or materialized view object while connected to the target master site or master materialized view site, then you must remove the related metadata at the master site or master materialized view site manually. Cleaning up the metadata also ensures that you are not needlessly maintaining master table or master materialized view changes to a materialized view log. The following sections describe how to clean up your master site or master materialized view site after dropping a materialized view group or object.
If you have executed the steps described in "Dropping a Materialized View Group Created Manually" and were not connected to the master site or master materialized view site, then you are encouraged to complete the following steps to clean up the target master site or master materialized view site.
Meet the following requirements to complete these actions:
Executed As: Replication Administrator
Executed At: Master Site or Master Materialized View Site for Target Materialized View Site
Replication Status: Normal
Complete the following steps to clean up a master site or master materialized view site after dropping a materialized view group:
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 ******************************
*/
SET ECHO ON SPOOL cleanup_master1.out CONNECT repadmin@orc1.example.com /*
*/
BEGIN DBMS_REPCAT.UNREGISTER_MVIEW_REPGROUP ( gname => 'hr_repg', mviewsite => 'mv1.example.com'); END; / /*
Execute the PURGE_MVIEW_FROM_LOG procedure for each materialized view that was in the materialized view groups you unregistered in Step 2.
Note:
If for some reason unregistering the materialized view group fails, then you should still complete this step.See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about theDBMS_MVIEW package
*/
BEGIN
DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
mviewowner => 'hr',
mviewname => 'countries_mv1',
mviewsite => 'mv1.example.com');
END;
/
BEGIN
DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
mviewowner => 'hr',
mviewname => 'departments_mv1',
mviewsite => 'mv1.example.com');
END;
/
BEGIN
DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
mviewowner => 'hr',
mviewname => 'employees_mv1',
mviewsite => 'mv1.example.com');
END;
/
BEGIN
DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
mviewowner => 'hr',
mviewname => 'jobs_mv1',
mviewsite => 'mv1.example.com');
END;
/
BEGIN
DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
mviewowner => 'hr',
mviewname => 'job_history_mv1',
mviewsite => 'mv1.example.com');
END;
/
BEGIN
DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
mviewowner => 'hr',
mviewname => 'locations_mv1',
mviewsite => 'mv1.example.com');
END;
/
BEGIN
DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
mviewowner => 'hr',
mviewname => 'regions_mv1',
mviewsite => 'mv1.example.com');
END;
/
SET ECHO OFF
SPOOL OFF
/************************* END OF SCRIPT **********************************/
If you have executed the steps described in "Dropping Objects at a Materialized View Site" and were not connected to the master site or master materialized view site, then you are encouraged to complete the following steps to clean up the target master site or master materialized view site.
Meet the following requirements to complete these actions:
Executed As: Replication Administrator
Executed At: Master Site or Master Materialized View Site for Target Materialized View Site
Replication Status: Normal
Complete the following steps to clean up a master site or master materialized view site after dropping an individual materialized view.
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 ******************************
*/
SET ECHO ON SPOOL cleanup_master2.out CONNECT repadmin@orc1.example.com /*
*/
BEGIN
DBMS_MVIEW.UNREGISTER_MVIEW (
mviewowner => 'hr',
mviewname => 'employees_mv1',
mviewsite => 'mv1.example.com');
END;
/
/*
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about theDBMS_MVIEW packageNote:
If for some reason unregistering the materialized view fails, then you should still complete this step.See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about theDBMS_MVIEW package*/ BEGIN DBMS_MVIEW.PURGE_MVIEW_FROM_LOG ( mviewowner => 'hr', mviewname => 'employees_mv1', mviewsite => 'mv1.example.com'); END; / SET ECHO OFF SPOOL OFF /************************* END OF SCRIPT **********************************/
The following sections explain how to manage materialized view logs:
After creating a materialized view log, you can alter its storage parameters and support for corresponding materialized views. The following sections explain more about altering materialized view logs. Only the following users can alter a materialized view log:
The owner of the master table or master materialized view.
A user with the SELECT privilege for the master table or master materialized view and ALTER privilege on the MLOG$_master_name, where master_name is the name of the master for the materialized view log. For example, if the master table is employees, then the materialized view log table name is MLOG$_employees.
To alter a materialized view log's storage parameters, use the ALTER MATERIALIZED VIEW LOG statement. For example, the following statement alters a materialized view log on the employees table in the hr schema:
ALTER MATERIALIZED VIEW LOG ON hr.employees PCTFREE 25 PCTUSED 40;
To add new columns to a materialized view log, use the SQL statement ALTER MATERIALIZED VIEW LOG. For example, the following statement alters a materialized view log on the customers table in the sales schema:
ALTER MATERIALIZED VIEW LOG ON hr.employees ADD (department_id);
See Also:
Oracle Database Advanced Replication for more information about adding columns to a materialized view logOracle automatically tracks which rows in a materialized view log have been used during the refreshes of materialized views, and purges these rows from the log so that the log does not grow endlessly. Because multiple simple materialized views can use the same materialized view log, rows already used to refresh one materialized view might still be needed to refresh another materialized view. Oracle does not delete rows from the log until all materialized views have used them.
For example, suppose two materialized views were created against the customers table in a master site. Oracle refreshes the customers materialized view at the spdb1 database. However, the server that manages the master table and associated materialized view log does not purge the materialized view log rows used during the refresh of this materialized view until the customers materialized view at the spdb2 database also refreshes using these rows.
Because Oracle must wait for all dependent materialized views to refresh before purging rows from a materialized view log, unwanted situations can occur that cause a materialized view log to grow indefinitely when multiple materialized views are based on the same master table or master materialized view.
For example, such situations can occur when multiple materialized views are based on a master table or master materialized view and one of the following conditions is true:
One materialized view is not configured for automatic refreshes and has not been manually refreshed for a long time.
One materialized view has an infrequent refresh interval, such as every year (365 days).
A network failure has prevented an automatic refresh of one or more of the materialized views based on the master table or master materialized view.
A network or site failure has prevented a master table or master materialized view from becoming aware that a materialized view has been dropped.
Note:
If you purge orTRUNCATE a materialized view log before a materialized view has refreshed the changes that were deleted, then the materialized view must perform a complete refresh.Always try to keep a materialized view log as small as possible to minimize the database space that it uses. To remove rows from a materialized view log and make space for newer log records, you can perform one of the following actions:
Refresh the materialized views associated with the log so that Oracle can purge rows from the materialized view log.
Manually purge records in the log by deleting rows required only by the nth least recently refreshed materialized views.
To manually purge rows from a materialized view log, execute the PURGE_LOG procedure of the DBMS_MVIEW package at the database that contains the log. For example, to purge entries from the materialized view log of the customers table that are necessary only for the least recently refreshed materialized view, execute the following procedure:
BEGIN DBMS_MVIEW.PURGE_LOG ( master => 'hr.employees', num => 1, flag => 'DELETE'); END; /
Only the owner of a materialized view log or a user with the EXECUTE privilege for the DBMS_MVIEW package can purge rows from the materialized view log by executing the PURGE_LOG procedure.
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about theDBMS_MVIEW packageIf a materialized view log grows and allocates many extents, then purging the log of rows does not reduce the amount of space allocated for the log. In such cases, you should truncate the materialized view log. Only the owner of a materialized view log or a user with the DELETE ANY TABLE system privilege can truncate a materialized view log.
To reduce the space allocated for a materialized view log by truncating it, complete the following steps:
For example, issue a statement similar to the following:
LOCK TABLE hr.employees IN EXCLUSIVE MODE;
For example, issue a statement similar to the following:
CREATE TABLE hr.templog AS SELECT * FROM hr.MLOG$_employees;
For example, issue a statement similar to the following:
TRUNCATE TABLE hr.MLOG$_employees;
Perform this step so that you do not have to perform a complete refresh of the dependent materialized views.
For example, issue statements similar to the following:
INSERT INTO hr.MLOG$_employees SELECT * FROM hr.templog; DROP TABLE hr.templog;
You can accomplish this by performing a rollback:
ROLLBACK;
Note:
Any changes made to the master table or master materialized view between the time you copy the rows to a new location and when you truncate the log do not appear until after you perform a complete refresh.To improve performance and optimize disk use, you can periodically reorganize master tables. This section describes how to reorganize a master and preserve the fast refresh capability of associated materialized views.
Note:
These sections do not discuss online redefinition of tables. Online redefinition is not allowed on master tables with materialized view logs, master materialized views, or materialized views. Online redefinition is allowed only on master tables that do not have materialized view logs. See the Oracle Database Administrator's Guide for more information about online redefinition of tables.When you reorganize a table, any ROWID information of the materialized view log must be invalidated. Oracle detects a table reorganization automatically only if the table is truncated as part of the reorganization.
If the table is not truncated, then Oracle must be notified of the table reorganization. To support table reorganizations, two procedures in the DBMS_MVIEW package, BEGIN_TABLE_REORGANIZATION and END_TABLE_REORGANIZATION, notify Oracle that the specified table has been reorganized. The procedures perform clean-up operations, verify the integrity of the logs and triggers that the fast refresh mechanism needs, and invalidate the ROWID information in the table's materialized view log. The inputs are the owner and name of the master to be reorganized. There is no output.
When a table is truncated, its materialized view log is also truncated. However, for primary key materialized views, you can preserve the materialized view log, allowing fast refreshes to continue. Although the information stored in a materialized view log is preserved, the materialized view log becomes invalid for rowids when the master is truncated. The rowid information in the materialized view log will seem to be newly created and cannot be used by rowid materialized views for fast refresh.
The PRESERVE MATERIALIZED VIEW LOG option is the default. Therefore, if you specify the PRESERVE MATERIALIZED VIEW LOG option or no option, then the information in the master's materialized view log is preserved, but current rowid materialized views can use the log for a fast refresh only after a complete refresh has been performed.
Note:
To ensure that any previously fast refreshable materialized view is still refreshable, follow the guidelines in "Methods of Reorganizing a Database Table".If the PURGE MATERIALIZED VIEW LOG option is specified, then the materialized view log is purged along with the master.
Either of the following two statements preserves materialized view log information when the master table named employees is truncated:
TRUNCATE TABLE hr.employees PRESERVE MATERIALIZED VIEW LOG; TRUNCATE TABLE hr.employees;
The following statement truncates the materialized view log along with the master table:
TRUNCATE TABLE hr.employees PURGE MATERIALIZED VIEW LOG;
Oracle provides four table reorganization methods that preserve the capability for fast refresh. These appear in the following sections. Other reorganization methods require an initial complete refresh to enable subsequent fast refreshes.
Note:
Do not use Direct Loader during a reorganization of a master. Direct Loader can cause reordering of the columns, which could invalidate the log information used in subquery and LOB materialized views.Complete the following steps:
Call DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION for table employees.
Rename table employees to employees_old.
Create table employees as SELECT * FROM employees_old.
Call DBMS_MVIEW.END_TABLE_REORGANIZATION for new table employees.
Caution:
When a table is renamed, its associated PL/SQL triggers are also adjusted to the new name of the table.Ensure that no transaction is issued against the reorganized table between calling BEGIN_TABLE_REORGANIZATION and END_TABLE_REORGANIZATION.
Complete the following steps:
Call DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION for table employees.
Export table employees.
Truncate table employees with PRESERVE MATERIALIZED VIEW LOG option.
Import table employees using conventional path.
Call DBMS_MVIEW.END_TABLE_REORGANIZATION for new table employees.
Caution:
When you truncate masters as part of a reorganization, you must use thePRESERVE MATERIALIZED VIEW LOG clause of the truncate table DDL.Ensure that no transaction is issued against the reorganized table between calling BEGIN_TABLE_REORGANIZATION and END_TABLE_REORGANIZATION.
Complete the following steps:
Call DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION for table employees.
Export table employees.
Rename table employees to employees_old.
Import table employees using conventional path.
Call DBMS_MVIEW.END_TABLE_REORGANIZATION for new table employees.
Caution:
When a table is renamed, its associated PL/SQL triggers are also adjusted to the new name of the table.Ensure that no transaction is issued against the reorganized table between calling BEGIN_TABLE_REORGANIZATION and END_TABLE_REORGANIZATION.
Complete the following steps:
Call DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION for table employees.
Select contents of table employees to a flat file.
Rename table employees to employees_old.
Create table employees with the same shape as employees_old.
Run SQL*Loader using conventional path.
Call DBMS_MVIEW.END_TABLE_REORGANIZATION for new table employees.
Caution:
When a table is renamed, its associated PL/SQL triggers are also adjusted to the new name of the table.Ensure that no transaction is issued against the reorganized table between calling BEGIN_TABLE_REORGANIZATION and END_TABLE_REORGANIZATION.
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about theDBMS_MVIEW packageYou can delete a materialized view log regardless of its master or any existing materialized views. For example, you might decide to drop a materialized view log if one of the following conditions is true:
All materialized views of a master have been dropped.
All materialized views of a master are to be refreshed using complete refresh, not fast refresh.
A master no longer supports materialized views that require fast refreshes.
Here, a master can be a master table or a master materialized view. To delete a materialized view log, execute the DROP MATERIALIZED VIEW LOG statement in SQL*Plus. For example, the following statement deletes the materialized view log for a table named customers in the sales schema:
DROP MATERIALIZED VIEW LOG ON hr.employees;
Only the owner of the master or a user with the DROP ANY TABLE system privilege can drop a materialized view log.
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.example.com and the master site is orc1.example.com.
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.
See Also:
For more information about setting up a master site and creating proxy users at a master site, see "Setting Up Master Sites"
For more information about setting up materialized view sites, see "Setting Up Materialized View Sites"
Complete the following steps to set up a materialized view site named mview.example.com.
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 *********************************
*/
SET ECHO ON SPOOL offline.out CONNECT repadmin@orc1.example.com /*
If materialized view logs do not 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; /*
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@orc1.example.com
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;
ACCEPT password PROMPT 'Enter password for user: ' HIDE
CREATE USER temp_schema IDENTIFIED BY &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@orc1.example.com;
/*
These materialized views contain the data that you transfer to your new materialized view site using the Export utility.
Note:
Ensure that theSELECT statements include the database link. In this example, the database link is orc1.example.com.
*/
CREATE MATERIALIZED VIEW temp_schema.countries
REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT *
FROM hr.countries@orc1.example.com;
CREATE MATERIALIZED VIEW temp_schema.departments
REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT *
FROM hr.departments@orc1.example.com;
CREATE MATERIALIZED VIEW temp_schema.employees
REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT *
FROM hr.employees@orc1.example.com;
CREATE MATERIALIZED VIEW temp_schema.jobs
REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT *
FROM hr.jobs@orc1.example.com;
CREATE MATERIALIZED VIEW temp_schema.job_history
REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT *
FROM hr.job_history@orc1.example.com;
CREATE MATERIALIZED VIEW temp_schema.locations
REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT *
FROM hr.locations@orc1.example.com;
CREATE MATERIALIZED VIEW temp_schema.regions
REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT *
FROM hr.regions@orc1.example.com;
/*
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@orc1.example.com CREATE DIRECTORY DPUMP_DIR AS '/usr/dpump_dir'; CONNECT system@mview.example.com 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.
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 SCHEMAS=temp_schema DIRECTORY=DPUMP_DIR DUMPFILE=temp_schema.dmp
See Also:
Oracle Database Utilities for information about performing a Data Pump export*/ PAUSE Press <RETURN> to continue when the export is complete. /*
*/
CONNECT system@mview.example.com /*
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; /*
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 &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@mview.example.com
CREATE DATABASE LINK orc1.example.com CONNECT TO hr IDENTIFIED by &password;
/*
*/
CONNECT mviewadmin@mview.example.com /*
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.example.com', propagation_mode => 'ASYNCHRONOUS'); END; / /*
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; / /*
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. /*
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 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.
See Also:
Oracle Database Utilities for information about performing a Data Pump import*/ PAUSE Press <RETURN> to continue when the import is complete. /*
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; / /*
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; / /*
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'); /*
*/
CONNECT system@orc1.example.com /*
*/
DROP USER temp_schema CASCADE; SET ECHO OFF SPOOL OFF /************************* END OF SCRIPT **********************************/
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.example.com and the master site is orc1.example.com.
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.
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.See Also:
Oracle Database Advanced Replication for a complete description of using group owners and the advantages of using multiple data sets
For more information about setting up a master site and creating proxy users at a master site, see "Setting Up Master Sites"
For more information about setting up materialized view sites, see "Setting Up Materialized View Sites"
/************************* BEGINNING OF SCRIPT ******************************
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 exists at the materialized view site, then grant any necessary privileges.
*/
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 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@mv1.example.com CREATE DATABASE LINK orc1.example.com CONNECT TO proxy_refresher IDENTIFIED BY &password; /*
*/
CONNECT mviewadmin@mv1.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 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.example.com', propagation_mode => 'ASYNCHRONOUS', gowner => 'bob'); END; / BEGIN DBMS_REPCAT.CREATE_MVIEW_REPGROUP ( gname => 'hr_repg', master => 'orc1.example.com', propagation_mode => 'ASYNCHRONOUS', gowner => 'jane'); END; / /*
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.
Caution:
Each object must have a unique name. When using agowner to create multiple materialized view groups, duplicate object names could become a problem. To avoid any object-naming conflicts, you might want to append the gowner value to the end of the object name that you create, as illustrated in the following procedures (that is, create materialized view hr.countries_bob). Such a naming method ensures that you do not create any objects with conflicting names.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.example.com; CREATE MATERIALIZED VIEW hr.departments_bob REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.departments@orc1.example.com; CREATE MATERIALIZED VIEW hr.employees_bob REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.employees@orc1.example.com; CREATE MATERIALIZED VIEW hr.jobs_bob REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.jobs@orc1.example.com; CREATE MATERIALIZED VIEW hr.job_history_bob REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.job_history@orc1.example.com; CREATE MATERIALIZED VIEW hr.locations_bob REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.locations@orc1.example.com; CREATE MATERIALIZED VIEW hr.regions_bob REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.regions@orc1.example.com; /*
*/
CREATE MATERIALIZED VIEW hr.departments_jane REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.departments@orc1.example.com; CREATE MATERIALIZED VIEW hr.employees_jane REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM hr.employees@orc1.example.com; /*
*/
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; / /*
*/
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 /*
See Also:
Chapter 5, "Creating a Materialized View Group" (Step 6) for more information about adding materialized views to a refresh group/************************* END OF SCRIPT **********************************/