8
Managi ng a Materialized View Replication Environment

Materialized vie w replication provides the flexibility to build data sets to meet the needs of your users and front-end applications, while still mee ting the requirements of your security configuration. This chapter describes how to manage materialized view sites with the replicati on management API.

This chapter contains these topics:

Refreshing Materialized Views
Changing a Materialized View Group's Master Site
Dropping Materialized View Groups and Objects
Managing Materialized View Logs
< a href="rarmanmv.htm#12817">Performing an Offline Instantiation of a Materialized View Site Using Export/Import
Using a Group Owner for a Materialized View Group

Refreshing Materializ ed Views

Refreshing a materialized view synchronizes the data in the m aterialized 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 materialize d view at a materialized view site, then Oracle Corporation recommends using refresh groups so that the data is transactionally consi stent in all of the materialized views used by the application.

The following example refresh es 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 (
<
a name="34855">     list   =>  'hr.departments_mv', 
     method =>  '?');
END;
/

Note:

Do not use the DBMS_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:

PL/SQ L Packages and Types Reference for more information about the DBMS_MVIEW package

Changing a Material ized View Group's Master Site

To change the master site of a materiali zed 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
<
/a>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 databas e must be a master site in the replication environment. When you call this procedure, Oracle uses the new master to perform a full re fresh of each materialized view in the local materialized view group. Make sure you have set up the materialized view site to use the new master site before you run the SWITCH_MVIEW_MASTER procedure.

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

Note:

You cannot switch the master of materi alized views that are based on other materialized views (level 2 and greater materialized views). Such a materialized view must be dr opped and re-created if you want to base it on a different master.

See Also:

"Setting Up Mat erialized View Sites"

Dropping Materialized View Groups and Objects

You may 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 Deploym ent Template

Dropping a Materialized View Group Created with a Deployment Template

Using the Public Version of DROP_SITE_INSTANTIATION

Meet the following requirements to complete these actions:

< strong class="Bold">Executed As:

Materialized View Grou p Owner at Master Site
Materialized View Administrator at Materialized View Site

Executed At:

Master Site for Target Materialized View Site
Material ized 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 "EN D 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 to the master site as the materialized view group owner.

*/

SET ECHO ON

SPOOL drop_mv_group_public.out

CONNECT hr/hr@orc3.world


/*

Step 2 Drop the in stantiated materialized view site from the master site.

*/

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

/*

Step 3 Connect to the remote materialized view site as the materialized view administrator.

< pre class="CE">*/ CONNECT mviewadmin/mviewadmin@mv4.world /*

If you are not able to connect to the r emote materialized view site, then the target materialized view group cannot refresh, but the existing data still remains at the mate rialized view site.

Step 4 Drop the materialized view group.

*/

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

/*

If you want to physically remove the contents of the materialized view group from the materialized view database, then be sure that you specify TRUE for the drop_contents parameter.

Step 5 Remove the refresh group.

Connect as the refresh group owner and remove the refresh group.

*/

CONNECT hr/hr@mv4.world

BEGIN

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

SET ECHO OFF

SPOOL OFF

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

Using the Private Version of DROP_SITE_INSTANTIATION

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 A dministrator at Materialized View Site

Executed At:

< ul class="LB1">

Master Site for Target Materialized View Site

Materialized View Site

Replication S tatus: 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 S CRIPT" 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 environ ment.

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

Step 1 Connect to the mast er site as the replication administrator.

*/

SET ECHO ON

SPOOL drop_mv_group_private.out

CONNEC
T repadmin/repadmin@orc1.world

/*

Step 2 Drop the instantiated materialized view site from the master site.

*/

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

/*

Step 3 Connect to the remote materialized view site as the materialized view administrator.

*/

CONNECT mviewadmin/mviewadmin@mv4.world

/*

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.

Step 4 Drop the materialized view group.

*/

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

/*

If you want to physically remove the contents of the mat erialized view group from the materialized view database, then be sure that you specify TRUE for the drop_contents parameter.

Step 5 Remove the refresh grou p.

Connect as the refresh group owner and remove the refresh group.

*/

CONNECT hr/hr@mv4.world

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

SET ECHO OFF

SPOOL OFF

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

Dropping a Materialized View Group or Objects Created Manually

The most secure method of removing replication support for a materialized view site is to physically drop the replicated objects or groups at the ma terialized view site. The following two sections describe how to drop these objects and groups while connected to the materialized vi ew group.

Ideally, these procedures should be executed while the materialized view is connect ed to its target master site or master materialized view site. A connection ensures that any related metadata at the master site or m aster 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.

Dropping a Materialized View Group Created Manually

When it becomes necessary to remove a materialized view group from a materialized vi ew site, use the DROP_MVIEW_REPGROUP procedure to drop a materialized view group. When you execute this procedure and ar e connected to the target master site or master materialized view site, the metadata for the target materialized view group at the ma ster site or master materialized view site is removed. If you cannot connect, then see "Cleaning Up a Ma ster Site or Master Materialized View Site" for more information.

Meet the following requirements to complete these actions:

Exe cuted 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:

Step 1 Connect to the materialized view site as the materi alized view administrator.

CONNECT mviewadmin/mviewadmin@mv1.world

Step 2 Drop the materialized view group.

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

/

If you want to physically remove the contents of the materialized v iew group from the materialized view database, then be sure that you specify TRUE for the drop_contents par ameter.

Dropp ing Objects at a Materialized View Site

When it becomes necessary to r emove 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, th e metadata for the target materialized view at the master site or master materialized view site is removed. If you cannot connect, th en 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 individu al materialized view at a materialized view site.

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

CONNECT mviewadmin/mviewadmin@mv1.world

Step 2 Drop the materialized view.

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

If you want to physically remove the contents of the materialized view from the materialized view database, then be sure that you specify TRUE for the drop_con tents parameter.

Cleaning Up a Master Site or Master Materialized View Site

If you are unable to drop a materialized view group or materialized view object while connected to the target master site or master m aterialized view site, then you must remove the related metadata at the master site or master materialized view site manually. Cleani ng up the metadata also ensures that you are not needlessly maintaining master table or master materialized view changes to a materia lized view log. The following sections describe how to clean up your master site or master materialized view site after dropping a ma terialized view group or object.

Cleaning Up After Dropping a Materialized View Group

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:

Exec uted 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 o r master materialized view site after dropping a materialized view group:

Note:

If you are viewing this document online, then y ou 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 to the master site or master materialized view site as the replication administrator.

< pre class="CE">*/ SET ECHO ON S POOL cleanup_master1.out CONNECT repadmin/repadmin@orc1.world /*

Step 2 Unregister the materializ ed view groups.

*/

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

/*

Step 3 Purge the materialized view logs of the entries that were marked for the target materialized views.

Execute the PU RGE_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.

*/

BEGIN
   DBMS_MVIEW.PURG
E_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 => 'job
s_mv1', 
      mviewsite => 'mv1.world');
END;
/

BEGIN
   DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
      mviewowner => 'hr',
<
a name="33248">      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',
      mviewn
ame => 'regions_mv1', 
      mviewsite => 'mv1.world');
END;
/

SET ECHO OFF

SPOOL OFF

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

If you have executed the steps described in "Drop ping Objects at a Materialized View Site" and were not connected to the master site or master ma terialized view site, then you are encouraged to complete the following steps to clean up the target master site or master materializ ed 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 th e following steps to clean up a master site or master materialized view site after dropping an individual materialized view.

See Also:

PL/SQL Packages and Types Reference for more information about the DBMS_MVIEW package

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 "E ND 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 to the master site or master materialized view s ite as the replication administrator.

*/

SET
 ECHO ON

SPOOL cleanup_master2.out

CONNECT repadmin
/repadmin@orc1.world

/*

Step 2 Unregister the materialized view.

*/

<
a name="31334">BEGIN
   DBMS_MVIEW.UNREGISTER_MVIEW (
      mviewowner => 'hr',
      mviewname => 'employees_mv1',
      mviewsite => 'mv1.world');
END;

/

/*

See Also:

PL/SQL Packages and Types Reference for more information about th e DBMS_MVIEW package

Step 3 Purge the associated materialized view log of the entries that were marked for the target materialized views.< /h5>

Note: < p class="NB">If for some reason unregistering the materialized view fails, then you should still complete this step.

See Also: < a name="36910">

PL/SQL Packages and Types Reference for more information about the DBMS_MVIEW package

*/

BEGIN
   DBMS_MVIEW.PURGE_MV
IEW_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

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 tabl e 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_nam e, 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.

Altering Materialized View Log Storage Parameters

To alter a materialized view log's storage paramete rs, use the ALTER MATERIALIZED VIEW LOG statement. For example, the following sta tement alters a materialized view log on the employees table in the hr schema:

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

Altering a Materiali zed View Log to Add Columns

To add new columns to a materialized view log, use the SQL statement ALTER MATERIALIZED VIEW LOG. For example, the followin g 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:

< em class="Italic">Oracle Database Advanced Replicati on for more information about adding columns to a materialized view log

Managing Materialized View Log Space

Oracle automatically tracks which rows in a materialized view log have bee n used during the refreshes of materialized views, and purges these rows from the log so that the log does not grow endlessly. Becaus e multiple simple materialized views can use the same materialized view log, rows already used to refresh one materialized view may s till be needed to refresh another materialized view. Oracle does not delete rows from the log until all mater ialized views have used them.

For example, suppose two materialized views were created agains t 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 materiali zed view log rows used during the refresh of this materialized view until the customers materialized view at the s pdb2 database also refreshes using these rows.

Because Oracle must wait for all depend ent materialized views to refresh before purging rows from a materialized view log, unwanted situations can occur that cause a materi alized 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 more than one materialized view is based on a ma ster 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 ti me.
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 or TRUNCATE a materialized view log before a materialized view has refreshed the changes that were deleted, then the materialized view must perform a complete refresh.

Purging Rows fro m a Materialized View Log

Always try to keep a materialized view log a s 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:

Refr esh 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 lea st 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 DB MS_MVIEW package can purge rows from the materialized view log by executing the PURGE_LOG procedure.

See Also:

PL/SQL Packages and Typ es Reference for more information about the DBMS_MVIEW package

Truncating a Materialized View Log

If a materialized view log grows and allocates many extents, then purgin g 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 pri vilege can truncate a materialized view log.

To reduce the space allocated for a materialized view log by truncating it, complete the following steps:

Step 1 Acquire an exclusive lock on the master table or master materialized view to prevent updates during the following pro cess.

For example, issue a statement similar to the following:

LOCK TABLE hr.employees IN EXCLUSIVE MODE;

Step 2 Using a second database session, copy the rows in the materialized view log (in other words, the MLOG$ table) t o a temporary table.

For example, issue a statement similar to the following:

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

Step 3 Using the second session, truncate the log using the SQL statement TRUNCATE TA BLE.

For example, issue a statement similar to the following:

TRUNCATE TABLE hr.MLOG$_employees;

Step 4 Using the second session, reinsert the old rows.

Perform this step so th at you do not have to perform a complete refresh of the dependent materialized views.

For exa mple, issue statements similar to the following:

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

DROP TABLE hr.templog;

Step 5 Using the first session, release the exclusive lock on the master table or master materialized view.

You can accomplish this by performing a rollback:

ROLLBACK;

N ote:

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.

Reorganizing Master Tables that Have Materialized View Logs

To improve performance and optimize disk use, you can periodically reorganize master tables. This section d escribes how to reorganize a master and preserve the fast refresh capability of associated materialized views.

Note:

These sect ions do not discuss online redefinition of tables. Online redefinition is not allowed on master tables with materialized view logs, m aster materialized views, or materialized views. Online redefinition is allowed only on master tables that do not have materialized v iew logs. See the Oracle Datab ase Administrator's Guide for more information about online redefinition of tables.

Reorganization Notificat ion

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 t he table reorganization. To support table reorganizations, two procedures in the DBMS_MVIEW package, BEGIN_TABLE_R EORGANIZATION and END_TABLE_REORGANIZATION, notify Oracle that the specified table has been reorganized. The proc edures perform clean-up operations, verify the integrity of the logs and triggers that the fast refresh mechanism needs, and invalida te the ROWID information in the table's materialized view log. The inputs are the owner and name of the master to be reo rganized. There is no output.

< tr class="NoteAlso">

See Also:< /strong>
"Method 2 for Reorganizing Table employees"

Truncating Masters

When a tabl e is truncated, its materialized view log is also truncated. However, for primary key materialized views, you can preserve the materi alized view log, allowing fast refreshes to continue. Although the information stored in a materialized view log is preserved, the ma terialized view log becomes invalid with respect to rowids when the master is truncated. The rowid information in the materialized vi ew 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 info rmation in the master's materialized view log is preserved, but current rowid materialized views can use the log for a fast refresh o nly after a complete refresh has been performed.

Note:

To ensure that any previously fast refreshable m aterialized view is still refreshable, follow the guidelines in "Methods of Reorganizing a Database Tabl e".

If the PURGE MATERIALIZED VIEW LOG option is specified, then the materialized view log is purged along w ith the master.


 
Examples

 
Either of the following two statements preserves materiali
zed view log information when the master table named employees is truncated:
TRU
NCATE 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;



Methods of Reorganizing a Database Table

 
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 sub
sequent fast refreshes.
 





Note:
 
Do not use Direct Loader during a reorganization of a master. Direct Load
er can cause reordering of the columns, which could invalidate the log information used in subquery and LOB materialized views.
<
hr>
 
Method 1 for Reorganizing Table employees

 
<
p class="BP">Complete the following steps:
 
Step 1
 Call DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION for table employees.
 
Step 2 Rename table employees to employees_old.
 
Step 3 Create table employees as SELECT * FROM employees_old.
 
Step 4 Call DBMS_MVIEW.END_TABLE_REORGANIZATION for new table employees.
 

<
/tr>




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 cal
ling BEGIN_TABLE_REORGANIZATION and END_TABLE_REORGANIZATION.
 
 
Complete the following steps:
 
Step 1 Call DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION for table employees.

 
Step 2 Export table employees.
 
Step 3 Truncate table employees with PRESERVE MATERIALI
ZED VIEW LOG option.
 
Step 4 Import table
employees using conventional path.
 
Step 5
 Call DBMS_MVIEW.END_TABLE_REORGANIZATION for new table employees.
 





Caution:  
When you truncate masters as part of a reor
ganization, you must use the PRESERVE MATERIALIZED VIEW LOG clause of the truncat
e table DDL.

 
Ensure that no transaction is issued against the re
organized table between calling BEGIN_TABLE_REORGANIZATION and END_TABLE_REORGANIZATION.
 
Method 3 f
or Reorganizing Table employees

 
Complete the following steps:

 
Step 1 Call DBMS_MVIEW.BEGIN_TABLE_REORGANIZATIO
N for table employees.
 
Step 2 Export tabl
e employees.
 
Step 3 Rename table employee
s to employees_old.
 
Step 4 Import table e
mployees using conventional path.
 
Step 5
Call DBMS_MVIEW.END_TABLE_REORGANIZATION for new table employees.
 





Caution:  
When a table is renamed, its associated PL/S
QL triggers are also adjusted to the new name of the table.

 
Ensu
re that no transaction is issued against the reorganized table between calling BEGIN_TABLE_REORGANIZATION and END_
TABLE_REORGANIZATION.
 
Method 4 for Reorganizing Table employees

 
Complete the following steps:
 
S
tep 1 Call DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION for table employees.
 
Step 2 Select contents of table employees to a flat file.
 
Step 3 Rename table employees to employees_old.
 
Step 4 Create table employees with the same shape as employees_old.
 
Step 5 Run SQL*Loader using conventional path.

 
Step 6 Call DBMS_MVIEW.END_TABLE_REORGANIZATION
 for new table employees.
 


<
tr class="Note">


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:  
PL/SQL Packages and Ty
pes Reference for more information about the DBMS_MVIEW package
 
Dropping a Materialized View Log


 
You can delete a materialized view log regardless of its master or any ex
isting materialized views. For example, you might decide to drop a materialized view log if one of the following conditions is true:<
/p>


All materialized views of a master have been dropped.
All materialized views of a master are to be completely refreshed, not fast refreshed.
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 v
iew log, execute the DROP MATERIALIZED VIEW LOG statement in SQL*Plus. For exampl
e, the following statement deletes the materialized view log for a table named customers in the sales schem
a:
DROP MATERIALIZED VIEW LOG ON hr.employees;



<
/a>
Only the owner of the master or a user with the DROP ANY TABLE system privil
ege can drop a materialized view log.
 
Performing an Offline Instantiation of a Materialized View Site Using Export/Import

Usi
ng 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 b
ased on the same replication group at a single materialized view site. Also, specifying group owners enables you to create multiple m
aterialized view groups that are based on the same replication group at a master site or master materialized view site. You accomplis
h 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 materia
lized 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 th
ey 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 t
his 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 inform
ation on setting up a master site and creating proxy users at a master site, see "Setting Up Master Sites
"
For more information on setting up material
ized view sites, see "Setting Up Materialized View Sites"

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



Step 1 Create a database link from the h
r schema to the master site
 
Before building your materialized view group, you must ma
ke sure that the replicated schema exists at the remote materialized view site and that the necessary database links have been create
d.
 
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/MANAGER@mv1.world

CREATE TABLESPACE demo_mv1
 DATAFILE 'demo_mv1.dbf' SIZE 10M AUTOEXTEND ON
 EXTENT MANAGEME
NT LOCAL AUTOALLOCATE;

CREATE TEMPORARY TABLESPACE temp_mv1
 TEMPFILE 't
emp_mv1.dbf' SIZE 5M AUTOEXTEND ON;

CREATE USER hr IDENTIFIED BY hr;

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, 
  CRE
ATE SEQUENCE, 
  CREATE TRIGGER, 
  CREATE VIEW, 
  CREATE SYNONYM, 
  ALTER SESSION,
  CREATE MATERIALIZED VIEW,
  ALTER ANY MATERIALIZED VIEW,
<
a name="39049">  CREATE DATABASE LINK
 TO hr;

/*





If it does not already exist, then create the database link for the replicated schema.<
/p>
 
Before building your materialized view group, you must make sure that the necessary database
links exist for the replicated schema. The owner of the materialized views needs a database link pointing to the proxy_refreshe
r that was created when the master site was set up.
*/

SET ECHO ON

SPOOL mv_group_owner.out

CONN
ECT hr/hr@mv1.world

CREATE DATABASE LINK orc1.world 
   CONNECT TO proxy
_refresher IDENTIFIED BY proxy_refresher;

/*



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

CONNECT mviewadmin/mviewadmin@mv1.world

/*



Step 3 Crea
te 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 grou
p 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 g
roup 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 specifi ed when you created the materialized view group in the previous procedures. After creating the materialized view groups, you can crea te 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 a go wner to create multiple materialized view groups, duplicate object names could become a problem. To avoid any object-naming co nflicts, you may want to append the gowner value to the end of the object name that you create, as illustrated in the fo llowing procedures (that is, create materialized view hr.countries_bob). Such a n aming 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 mater ialized view. In the following examples, hr is specified as the owner of the table in each query.

*/

CREATE MATERIALIZED VIEW hr.countries_bob 
  REFRES
H 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 UP
DATE 
  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_histo
ry@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 S
ELECT * FROM hr.regions@orc1.world;

/*

Step 5 Create the materialized views owned by jane.

*/

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

CREA
TE MATERIALIZED VIEW hr.employees_jane 
  REFRESH FAST WITH PRIMARY KEY FOR UPDATE 
  AS SELE
CT * FROM hr.employees@orc1.world;

/*

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

*/

BEGIN
DBMS_R
EPCAT.CREATE_MVIEW_REPOBJECT (
      gname => 'hr_repg',
      sname => 'hr',
      oname => 'countries_bob',
      type => 'SNAPSHOT',
      min_comm
unication => TRUE,
      gowner => 'bob');
END;
/
<
/a>
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
      gname => 'hr_repg',
      sname => 'hr',
      oname => 'departments_b
ob',
      type => 'SNAPSHOT',
      min_communication => TRUE,

   gowner => 'bob');
END;
/

BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (
      gname => 'hr_repg',
      sname => 'hr',
      oname => 'employees_bob',
      type => 'SNAP
SHOT',
      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_MVIE
W_REPOBJECT (
      gname => 'hr_repg',
      sname => 'hr',

    oname => 'regions_bob',
      type => 'SNAPSHOT',
      min_communication => TRU
E,
      gowner => 'bob');
END;
/

/*

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

*/

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

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

SET ECHO OFF

SPOOL OFF

/*

Step 8 Add your materiali zed views to a refresh group.

Se e Also:

Chapter 5, "Create Materialized View Gr oup" (Step 6) for more information about adding materialized views to a refresh group

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

8 Managi ng a Materialized View Replication Environment

Refreshing Materializ ed Views

Changing a Material ized View Group's Master Site

Dropping Materialized View Groups and Objects

Dropping a Materialized View Group Created with a Deployment Template

Using the Public Version of DROP_SITE_INSTANTIATION

Step 1 Connect to the master site as the materialized view group owner.

Step 2 Drop the in stantiated materialized view site from the master site.

Step 3 Connect to the remote materialized view site as the materialized view administrator.

Step 4 Drop the materialized view group.

Step 5 Remove the refresh group.

Using the Private Version of DROP_SITE_INSTANTIATION

Step 1 Connect to the mast er site as the replication administrator.

Step 2 Drop the instantiated materialized view site from the master site.

Step 3 Connect to the remote materialized view site as the materialized view administrator.

Step 4 Drop the materialized view group.

Step 5 Remove the refresh grou p.

Dropping a Materialized View Group or Objects Created Manually

Dropping a Materialized View Group Created Manually

Step 1 Connect to the materialized view site as the materi alized view administrator.

Step 2 Drop the materialized view group.

Dropp ing Objects at a Materialized View Site

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

Step 2 Drop the materialized view.

Cleaning Up a Master Site or Master Materialized View Site

Cleaning Up After Dropping a Materialized View Group

Step 1 Connect to the master site or master materialized view site as the replication administrator.

Step 2 Unregister the materializ ed view groups.

Step 3 Purge the materialized view logs of the entries that were marked for the target materialized views.

Step 1 Connect to the master site or master materialized view s ite as the replication administrator.

Step 2 Unregister the materialized view.

Managing Materialized View Logs

Altering Materialized View Logs

Altering Materialized View Log Storage Parameters

Altering a Materiali zed View Log to Add Columns

Managing Materialized View Log Space

Purging Rows fro m a Materialized View Log

Truncating a Materialized View Log

Step 1 Acquire an exclusive lock on the master table or master materialized view to prevent updates during the following pro cess.

Step 2 Using a second database session, copy the rows in the materialized view log (in other words, the MLOG$ table) t o a temporary table.

Step 3 Using the second session, truncate the log using the SQL statement TRUNCATE TA BLE.

Step 4 Using the second session, reinsert the old rows.

Step 5 Using the first session, release the exclusive lock on the master table or master materialized view.

Reorganizing Master Tables that Have Materialized View Logs

Reorganization Notificat ion

Truncating Masters

Examples

Methods of Reorganizing a Database Table

Method 1 for Reorganizing Table employees

Step 1 Call DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION for table employees.

Step 2 Rename table employees to employees_old.

Step 3 Create table employees as SELECT * FROM employees_old.

Step 1 Call DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION for table employees.

Step 2 Export table employees.

Step 3 Truncate table employees with PRESERVE MATERIALI ZED VIEW LOG option.

Step 4 Import table employees using conventional path.

Step 5 Call DBMS_MVIEW.END_TABLE_REORGANIZATION for new table employees.

Method 3 f or Reorganizing Table employees

Step 1 Call DBMS_MVIEW.BEGIN_TABLE_REORGANIZATIO N for table employees.

Step 2 Export tabl e employees.

Step 3 Rename table employee s to employees_old.

Step 4 Import table e mployees using conventional path.

Step 5 Call DBMS_MVIEW.END_TABLE_REORGANIZATION for new table employees.

Method 4 for Reorganizing Table employees

S tep 1 Call DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION for table employees.

Step 2 Select contents of table employees to a flat file.

Step 3 Rename table employees to employees_old.

Step 4 Create table employees with the same shape as employees_old.

Step 5 Run SQL*Loader using conventional path.

Step 6 Call DBMS_MVIEW.END_TABLE_REORGANIZATION for new table employees.

Dropping a Materialized View Log

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

Usi ng a Group Owner for a Materialized View Group

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

Step 2 Connect to the materialized view site as the materialized view administrator. */ CONNECT mviewadmin/mviewadmin@mv1.world /*

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

Step 4 Create the materialized views owned by bob.

Step 5 Create the materialized views owned by jane.

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

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

8
Managi ng a Materialized View Replication Environment

Step 2 Connect to the materialized view site as the materialized view administrator.
/ CONNECT mviewadmin/mviewadmin@mv1.world /