Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy




  Oracle Replication Tips by Burleson

Oracle OEM Replication

Our first task here is to create a Refresh Group.  In the mydb database, there currently are no refresh groups defined, so the drop down box is grayed out.  If your database contains refresh groups, then they will be displayed in the drop down box.  It is recommended that you create a new refresh group rather than mix the UMVs with other materialized views. 

It is also recommended that you separate the UMVs from any one-way Materialized Views on your database.  Although you can place over one hundred Materialized Views in a refresh group, remember that one process refreshes all members of the group and holds locks on tables during the refresh process. 

Experience shows that a number of smaller refresh groups works best because the task is spread across more job processes.  The only requirement you must consider when grouping objects in refresh groups is that they may depend on each other and they must refresh together to ensure data integrity.

Selecting the Create button will bring up the Create Refresh Group page (Figure 4.15).

Figure 4.15  Create Refresh Group

The refresh group is named REF_GROUPA. Make sure that you select the REPADMIN schema from the Schema combo-box.  If you want the refresh group to

be deleted when the last object is removed, select the checkbox. 

Change the refresh interval by pressing the Set button.  Here, the refresh interval is set to 1 min.  Because this refresh group will support UMVs, select the “Push changes…” and “Continue to refresh…” checkboxes.  Select the “Show SQL” button to see the actual SQL used to create the refresh group. 

If you select the Materialized View tab at the top of the window, you can see all available materialized views.  However, you will not find the views in the master replication group because they do not yet exist in the mydb database.  So, without adding Materialized Views, select Create.  OEM will display a dialog box showing that the refresh group was created and you will be returned to the Materialized View Defaults page (Figure 4.16).

Figure 4.16  Materialized View Defaults

Select the first three check boxes.  If you want to explicitly define a storage clause, check the fourth check box and then the Edit button to define the storage parameters.

Selecting Next takes you to Step 5 of the wizard and allows you to customize any of the UMVs before creation.  As you select each name, the checkboxes on the right will change to show the current setting.

If you are unable to set the Fast Refresh check box, it is because you forgot to create the materialized view log on that table.  You will need to cancel the wizard, drop the objects in mydb, and remove the table from the Master Group in navdb.  Then, create the materialized view log and add the table back to the master group. Restart the wizard on mydb (Figure 4.17).

Figure 4.17 Materialized View Customization

This page of the wizard also allows you to incorporate a WHERE clause in the updatable materialized view to restrict the rows included in the view. 

Selecting Next takes you to the last step.  Check the box in the center of the window to record a script of what OEM is about to execute.  The script is important because if the creation fails, you will use it to locate problems.

Selecting Finish will produce a list of the objects that will be created (Figure 4.18).

Figure 4.18  Rollup of Objects Being Created

When you select OK, OEM will create all the objects.  After finishing, you will have a chance to save the script to a file.  Again, this is important when tracking down any problems, and it is strongly suggested that you save the script (Figure 4.19).


This is an excerpt from Oracle Replication By Rampant TechPress (only $19.95).  You can click here to order a copy and get instant access to the code depot:

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks