5 Create Materialized View Group</titl e> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <meta http-equiv="Content-Language" content="EN"> <meta h ttp-equiv="Content-Style-Type" content="text/css"> <meta http-equiv="Content-Script-Type" content="text/javascript"> <meta name="robo ts" content="all" scheme="http://www.robotstxt.org/"> <meta name="doctitle" content="Oracle® Database Advanced Replication Manage ment API Reference 10g Release 1 (10.1)"> <meta name="partno" content="B10733-01"> <meta name="docid" content="REPMA"> <link rel="Sta rt" href="../../index.htm" title="Home" type="text/html"> <link rel="Copyright" href="../../dcommon/html/cpyr.htm" title="Copyright" type="text/html"> <link rel="Stylesheet" href="../../dcommon/css/doccd.css" title="Default" type="text/css"> <link rel="Contents" hre f="toc.htm" title="Contents" type="text/html"> <link rel="Index" href="index.htm" title="Index" type="text/html"> <link rel="Prev" hr ef="rardt.htm" title="Previous" type="text/html"> <link rel="Next" href="rarconfl.htm" title="Next" type="text/html"> <link rel="alte rnate" href="../b10733.pdf" title="PDF version" type="application/pdf"> </head> <body> <div class="header"> <p><a name="top" id="top" href="#BEGIN">Skip Headers</a></p> <table summary="" cellspacing="0" cellpadding="0" width="100%"> <tr> <td align="left" valign="top "><font color="#330099" face="Helvetica, Arial, sans-serif"><strong>Oracle® Database Advanced Replication Management API Referenc e<br> 10<i>g</i> Release 1 (10.1)</strong><br> Part Number B10733-01</font></td> <td valign="bottom" align="right"> <table summary=" " cellspacing="0" cellpadding="0" width="270"> <tr> <td align="center" valign="top"><a href="../../index.htm"><img src="../../dcommon /gifs/prodicon.gif" alt="Go to Documentation Home" border="0"><br> <font size="-2">Home</font></a></td> <td align="center" valign="to p"><a href="../../nav/portal_3.htm"><img src="../../dcommon/gifs/bookicon.gif" alt="Go to Book List" border="0"><br> <font size="-2"> Book List</font></a></td> <td align="center" valign="top"><a href="toc.htm"><img src="../../dcommon/gifs/conticon.gif" alt="Go to Tab le of Contents" border="0"><br> <font size="-2">Contents</font></a></td> <td align="center" valign="top"><a href="index.htm"><img src ="../../dcommon/gifs/indxicon.gif" alt="Go to Index" border="0"><br> <font size="-2">Index</font></a></td> <td align="center" valign= "top"><a href="../../mix$101/b12039/toc.htm"><img src="../../dcommon/gifs/mix.gif" alt="Go to Master Index" border="0"><br> <font siz e="-2">Master Index</font></a></td> <td align="center" valign="top"><a href="../../dcommon/html/feedback.htm"><img src="../../dc ommon/gifs/feedback.gif" alt="Go to Feedback page" border="0"><br> <font size="-2">Feedback</font></a></td></tr></table></td></tr></t able> <hr> <table summary="layout table" cellspacing="0" cellpadding="0" width="100%"> <tr> <td align="left" valign="top"> <table sum mary="layout table" cellspacing="0" cellpadding="0" align="left" width="90"> <tr> <td align="center" valign="top"><a href="rardt.htm" ><img src="../../dcommon/gifs/larrow.gif" alt="Go to previous page" border="0"><br> <font size="-2">Previous</font></a></td> <td alig n="center" valign="top"><a href="rarconfl.htm"><img src="../../dcommon/gifs/rarrow.gif" alt="Go to next page" border="0"><br> <font s ize="-2">Next</font></a></td></tr></table></td> <td align="right" valign="top"><a href="../b10733.pdf"><font size="-2">View PDF</font ></a></td></tr></table> <a name="BEGIN"></a></div> <div class="IND"> <a name="18377"></a>  <h1 class="Title"><font face="Arial, Helvetica, sans-serif" color="#330099">5<br> <a name="REPMA005">Create Materialized View Group </a></font></h1>  <a name="18372"></a> <p class="BP">This chapter illustrates how to create a materialized view gro up at a remote materialized view replication site.</p> <a name="18777"></a> <p class="BP">This chapter contains these topics:</p> <u l class="LB1"> <li class="LB1" type="disc"><a name="7348"></a><a href="rarmview.htm#17945">Overview of Creating a Materialized View G roup</a></li> <li class="LB1" type="disc"><a name="7349"></a><a href="rarmview.htm#15753">Creating a Materialized View Group</a></li> </ul> <a name="17128"></a> <p class="BP">Before you build materialized view environments, you must set up your master site, create a master group, and set up your intended materialized view sites. Also, if conflicts are possible at the master site due to activity a t the materialized view sites you are creating, then configure conflict resolution for the master tables of the materialized views be fore you create the materialized view group.</p> <a name="17943"></a> <div align="center"> <table class="NoteAlso" border="0" width= "80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to fo rmat a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name="17916"></a><font face="Arial, Helvetica, sans-serif"><strong class= "NH"><strong class="Bold">See Also</strong>:</strong></font> <a name="17917"></a> <ul class="NL"> <li class="NL" type="disc"><a name ="17924"></a><a href="rarrepsi.htm#8055">"Setting Up Master Sites"</a><a href="rarrepsi.htm#8055"></a></li> <li class="NL" type="disc "><a name="17931"></a><a href="rarmaste.htm#1004582">"Overview of Creating a Master Group"</a><a href="rarmaste.htm#1004582"></a></li > <li class="NL" type="disc"><a name="17938"></a><a href="rarrepsi.htm#18519">"Setting Up Materialized View Sites"</a><a href="rarrep si.htm#18519"></a></li> <li class="NL" type="disc"><a name="17942"></a><a href="rarconfl.htm#21415">Chapter 6, "Configure Confli ct Resolution"</a></li></ul></td></tr></table></div> <a name="17945"></a>  <h2 class="H1"><font face="Arial, Hel vetica, sans-serif" color="#330099">Overview of Creating a Materialized View Group</font></h2>  <a name="17946"></a> < p class="BP">After setting up your materialized view site and creating at least one master group, you are ready to create a materiali zed view group at a remote materialized view site. <a href="rarmview.htm#11874">Figure 5-1</a> illustrates the process of creati ng a materialized view group.</p> <a name="14545"></a> <div align="center"> <table class="NoteAlso" border="0" width="80%" cellpaddi ng="0" cellspacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> < tr class="NoteAlso"> <td class="NoteAlso"><a name="14548"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">See Also:< /strong></font> <a name="14549"></a> <p class="NB"><a href="rarrepsi.htm#133">Chapter 2, "Create Replication Site"</a> for info rmation about setting up a materialized view site, and see <a href="rarmaste.htm#1005371">Chapter 3, "Create a Master Group"</a> for information about creating a master group.</p></td></tr></table></div> <a name="11874"></a> <h4 class="FT"><font face="Arial, H elvetica, sans-serif"><em>Figure 5-1 Creating a Materialized View Group</em></font></h4> <a name="15750"><img src="repma027.gif" alt= "Text description of repma027.gif follows"></a> <p><a href="img_text/repma027.htm">Text description of the illustration repma027.gif </a></p> <a name="15753"></a>  <h2 class="H1"><font face="Arial, Helvetica, sans-serif" color="#330099">Creating a Materialized View Group</font></h2>  <a name="15788"></a> <p class="BP">This chapter guides you through the process of creating two materialized view groups at two different materialized view sites: <code>mv1.world</code> and <code>mv2.world</code> :</p> <ul class="LB1"> <li class="LB1" type="disc"><a name="16574"></a>The materialized view group at <code>mv1.world</code> is based on the objects in the <code>hr_repg</code> master group at the <code>orc1.world</code> master site.</li> <li class="LB1" type="disc" ><a name="16575"></a>The materialized view group at <code>mv2.world</code> is based on the objects in the <code>hr_repg</code> materi alized view group at the <code>mv1.world</code> materialized view site.</li></ul> <a name="16573"></a> <p class="BP">Therefore, the examples in this chapter illustrate how to create a multitier materialized view environment, where one or more materialized views are based on other materialized views.</p> <a name="16570"></a> <p class="BP">Complete the following steps to create these two material ized view groups.</p> <a name="15782"></a> <div align="center"> <table class="Note" border="0" width="80%" cellpadding="0" cellspaci ng="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="Note"> <td class="Note"> <hr> <a name="15777"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">Note:</strong></font> <a nam e="15778"></a> <p class="NB">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<a href="rarmview.htm#16043"></a> into a text editor and then edit the text to create a s cript for your environment.</p> <hr></td></tr></table></div> <pre class="CE"><a name="15783"></a>/************************* BEGINNING OF SCRIPT ****************************** </pre> <a name="16578"></a> <h3 class="H2"><font face="Arial, Helveti ca, sans-serif" color="#330099">Creating the Materialized View Group at mv1.world</font></h3>  <a name="16579"></a> <p class="BP">Complete the following steps to create the <code>hr_repg</code> materialized view group at the <code>mv1.world</code> mat erialized view site. This materialized view group is based on the <code>hr_repg</code> master group at the <code>orc1.world</code> ma ster site.</p> <a name="12492"></a> <h5 class="LST"><font face="Arial, Helvetica, sans-serif">Step 1 Create materialized view logs a t the master site.</font></h5> <a name="12503"></a> <p class="BP">If you want one of your master sites to support a materialized vie w site, then you need to create materialized view logs for each master table that is replicated to a materialized view. Recall from < a href="rarrepsi.htm#12531">Figure 2-1</a><a href="rarrepsi.htm#12531"></a> that <code>orc1.world</code> serves as the target ma ster site for the <code>mv1.world</code> materialized view site. The required materialized view logs must be created at <code>orc1.wo rld</code>.</p> <pre class="CE"><a name="15793"></a>*/ <a name="18069"></a> <a name="18070"></a>SET ECHO ON <a name="18071"></a> <a n ame="18072"></a>SPOOL create_mv_group.out <a name="18073"></a> <a name="12516"></a>CONNECT hr/hr@orc1.world <a name="12517"></a> <a n ame="12518"></a>CREATE MATERIALIZED VIEW LOG ON hr.countries; <a name="12519"></a>CREATE MATERIALIZED VIEW LOG ON hr.departments; <a name="16230"></a>CREATE MATERIALIZED VIEW LOG ON hr.employees; <a name="12520"></a>CREATE MATERIALIZED VIEW LOG ON hr.jobs; <a name=" 12521"></a>CREATE MATERIALIZED VIEW LOG ON hr.job_history; <a name="16078"></a>CREATE MATERIALIZED VIEW LOG ON hr.locations; <a name= "17532"></a>CREATE MATERIALIZED VIEW LOG ON hr.regions; <a name="12522"></a> <a name="15794"></a>/* </pre> <a name="15795"></a> <div align="center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a layout t able to format a note" title="This is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name="15815"></ a><font face="Arial, Helvetica, sans-serif"><strong class="NH">See Also:</strong></font> <a name="15812"></a> <p class="NB">The <cod e>CREATE</code> <code>MATERIALIZED</code> <code>VIEW</code> <code>LOG</code> statement in the <em class="Italic"><a class="xlinkSRC S QLRF01303" href="../../server$101/b10759/statements_6003.htm#SQLRF01303">Oracle Database SQL Reference</a></em> for detailed informat ion about this SQL statement</p></td></tr></table></div> <a name="12765"></a> <h5 class="LST"><font face="Arial, Helvetica, sans-ser if">Step 2 If they do not already exist, then create the replicated schema and its database link.</font></h5> <a name="17855"></a> < p class="BP">Before building your materialized view group, you must make sure that the replicated schema exists at the remote materia lized view site and that the necessary database links have been created.</p> <a name="18891"></a> <p class="BP">In this example, if the <code>hr</code> schema does not exist, then create the schema. If the <code>hr</code> schema already exists at the materialized v iew site, then grant any necessary privileges and go to the next task in this step.</p> <pre class="CE"><a name="13006"></a>*/ <a nam e="17756"></a> <a name="12768"></a>CONNECT SYSTEM/MANAGER@mv1.world <a name="16529"></a> <a name="16524"></a>CREATE TABLESPACE demo_m v1 <a name="16525"></a> DATAFILE 'demo_mv1.dbf' SIZE 10M AUTOEXTEND ON <a name="16526"></a> EXTENT MANAGEMENT LOCAL AUTOALLOCATE; <a name="16531"></a> <a name="16527"></a>CREATE TEMPORARY TABLESPACE temp_mv1 <a name="16528"></a> TEMPFILE 'temp_mv1.dbf' SIZE 5M AUTOE XTEND ON; <a name="12785"></a> <a name="16520"></a>CREATE USER hr IDENTIFIED BY hr; <a name="16502"></a> <a name="16503"></a>ALTER US ER hr DEFAULT TABLESPACE demo_mv1 <a name="16513"></a> QUOTA UNLIMITED ON demo_mv1; <a name="16506"></a> <a name="16507" ></a>ALTER USER hr TEMPORARY TABLESPACE temp_mv1; <a name="16460"></a> <a name="17958"></a>GRANT <a name="17959"></a> CREATE SESSIO N, <a name="17960"></a> CREATE TABLE, <a name="17961"></a> CREATE PROCEDURE, <a name="17962"></a> CREATE SEQUENCE, <a name="17 963"></a> CREATE TRIGGER, <a name="17964"></a> CREATE VIEW, <a name="17965"></a> CREATE SYNONYM, <a name="17966"></a> ALTER SE SSION, <a name="17967"></a> CREATE MATERIALIZED VIEW, <a name="17968"></a> ALTER ANY MATERIALIZED VIEW, <a name="17969"></a> CREAT E DATABASE LINK <a name="17970"></a> TO hr; <a name="17956"></a> <a name="15847"></a>/* <a name="18566"></a> </pre> <a name="16248" ></a> <p class="BP">If it does not already exist, then create the database link for the replicated schema.</p> <a name="16249"></a> <p class="BP">Before building your materialized view group, you must make sure that the necessary database links exist for the replic ated schema. The owner of the materialized views needs a database link pointing to the <code>proxy_refresher</code> that was created when the master site was set up.</p> <pre class="CE"><a name="14171"></a>*/ <a name="16258"></a> <a name="16261"></a>CONNECT hr/hr@mv 1.world <a name="15848"></a> <a name="15849"></a>CREATE DATABASE LINK orc1.world <a name="14169"></a> CONNECT TO proxy_refresher I DENTIFIED BY proxy_refresher; <a name="15850"></a> <a name="15851"></a>/* </pre> <a name="11487"></a> <h5 class="LST"><font face="Ar ial, Helvetica, sans-serif">Step 3 Create the materialized view group.</font></h5> <a name="12991"></a> <p class="BP">The following procedures must be executed by the materialized view administrator at the remote materialized view site.</p> <pre class="CE"><a name= "12992"></a>*/ <a name="15928"></a> <a name="15929"></a>CONNECT mviewadmin/mviewadmin@mv1.world <a name="15930"></a> <a name="15931"> </a>/* <a name="11326"></a> </pre> <a name="13029"></a> <p class="BP">The master group that you specify in the <code>gname</code> pa rameter must match the name of the master group that you are replicating at the target master site.</p> <pre class="CE"><a name="1593 3"></a>*/ <a name="11327"></a>BEGIN <a name="19248"></a><a href="rarrca28.htm#106990">DBMS_REPCAT.CREATE_MVIEW_REPGROUP (</a> <a name ="11329"></a> gname => 'hr_repg', <a name="11330"></a> master => 'orc1.world', <a name="11331"></a> propagation_ mode => 'ASYNCHRONOUS'); <a name="11332"></a>END; <a name="11333"></a>/ <a name="15934"></a> <a name="15935"></a>/* </pre> <a nam e="11501"></a> <h5 class="LST"><font face="Arial, Helvetica, sans-serif">Step 4 Create the refresh group.</font></h5> <a name="13069" ></a> <p class="BP">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.</p> <pre class="CE"><a name="13345"></a>*/ <a name="15957"></a> <a name="15958"></a>BEGIN <a name="19442"></a><a href="rarrefr5.htm#94080">DBMS_REFRESH.MAKE (</a> <a name="13351" ></a> name => 'mviewadmin.hr_refg', <a name="12547"></a> list => '', <a name="12557"></a> next_date => SYSDA TE, <a name="12558"></a> interval => 'SYSDATE + 1/24', <a name="11342"></a> implicit_destroy => FALSE, <a name="125 59"></a> rollback_seg => '', <a name="11343"></a> push_deferred_rpc => TRUE, <a name="12560"></a> refresh_after _errors => FALSE); <a name="11344"></a>END; <a name="11345"></a>/ <a name="15959"></a> <a name="15960"></a>/* </pre> <a name="115 15"></a> <h5 class="LST"><font face="Arial, Helvetica, sans-serif">Step 5 Add objects to the materialized view group.</font></h5> <a name="17869"></a> <p class="BP">Create the materialized views based on the master tables.</p> <a name="17791"></a> <p class="BP">Wh enever you create a materialized view, always specify the schema name of the table owner in the query for the materialized view. In t he following examples, <code>hr</code> is specified as the owner of the table in each query.</p> <pre class="CE"><a name="16283"></a> */ <a name="16284"></a> <a name="16287"></a>CREATE MATERIALIZED VIEW hr.countries_mv1 <a name="16297"></a> REFRESH FAST WITH PRIMAR Y KEY FOR UPDATE <a name="16306"></a> AS SELECT * FROM hr.countries@orc1.world; <a name="16311"></a> <a name="16314"></a>CREATE MAT ERIALIZED VIEW hr.departments_mv1 <a name="16315"></a> REFRESH FAST WITH PRIMARY KEY FOR UPDATE <a name="16316"></a> AS SELECT * FROM hr.departments@orc1.world; <a name="16312"></a> <a name="16321"></a>CREATE MATERIALIZED VIEW hr.employees_mv1 <a name="16322">< /a> REFRESH FAST WITH PRIMARY KEY FOR UPDATE <a name="16323"></a> AS SELECT * FROM hr.employees@orc1.world; <a name="16319"></a> < a name="16328"></a>CREATE MATERIALIZED VIEW hr.jobs_mv1 <a name="16329"></a> REFRESH FAST WITH PRIMARY KEY FOR UPDATE <a name="163 30"></a> AS SELECT * FROM hr.jobs@orc1.world; <a name="16326"></a> <a name="16335"></a>CREATE MATERIALIZED VIEW hr.job_history_mv1 <a name="16336"></a> REFRESH FAST WITH PRIMARY KEY FOR UPDATE <a name="16337"></a> AS SELECT * FROM hr.job_history@orc1.world; <a name="16333"></a> <a name="16346"></a>CREATE MATERIALIZED VIEW hr.locations_mv1 <a name="16347"></a> REFRESH FAST WITH PRIMARY KEY FOR UPDATE <a name="16348"></a> AS SELECT * FROM hr.locations@orc1.world; <a name="17540"></a> <a name="17543"></a>CREATE MATERIALI ZED VIEW hr.regions_mv1 <a name="17544"></a> REFRESH FAST WITH PRIMARY KEY FOR UPDATE <a name="17545"></a> AS SELECT * FROM hr.re gions@orc1.world; <a name="16373"></a> <a name="16374"></a>/* <a name="18576"></a> </pre> <a name="16282"></a> <p class="BP">Add the objects to the materialized view group.</p> <pre class="CE"><a name="11351"></a>*/ <a name="15979"></a> <a name="15980"></a>BEGIN <a name="19261"></a><a href="rarrca29.htm#107166">DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (</a> <a name="11353"></a> gname => 'hr_re pg', <a name="11354"></a> sname => 'hr', <a name="11355"></a> oname => 'countries_mv1', <a name="11356"></a> typ e => 'SNAPSHOT', <a name="11358"></a> min_communication => TRUE); <a name="11359"></a>END; <a name="15998"></a>/ <a name=" 16109"></a> <a name="16112"></a>BEGIN <a name="16117"></a><a href="rarrca29.htm#107166">DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (</a> <a n ame="16118"></a> gname => 'hr_repg', <a name="16119"></a> sname => 'hr', <a name="16120"></a> oname => 'depar tments_mv1', <a name="16121"></a> type => 'SNAPSHOT', <a name="16125"></a> min_communication => TRUE); <a name="16126 "></a>END; <a name="16110"></a>/ <a name="16138"></a> <a name="16141"></a>BEGIN <a name="16146"></a><a href="rarrca29.htm#107166">DBM S_REPCAT.CREATE_MVIEW_REPOBJECT (</a> <a name="16147"></a> gname => 'hr_repg', <a name="16148"></a> sname => 'hr', <a name="16149"></a> oname => 'employees_mv1', <a name="16150"></a> type => 'SNAPSHOT', <a name="16154"></a> min_c ommunication => TRUE); <a name="16155"></a>END; <a name="16139"></a>/ <a name="16159"></a> <a name="16162"></a>BEGIN <a name="1616 7"></a><a href="rarrca29.htm#107166">DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (</a> <a name="16168"></a> gname => 'hr_repg', <a nam e="16169"></a> sname => 'hr', <a name="16170"></a> oname => 'jobs_mv1', <a name="16171"></a> type => 'SNAPSHO T', <a name="16175"></a> min_communication => TRUE); <a name="16176"></a>END; <a name="16160"></a>/ <a name="16180"></a> <a n ame="16183"></a>BEGIN <a name="16188"></a><a href="rarrca29.htm#107166">DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (</a> <a name="16189"></a> gname => 'hr_repg', <a name="16190"></a> sname => 'hr', <a name="16191"></a> oname => 'job_history_mv1', <a name="16192"></a> type => 'SNAPSHOT', <a name="16196"></a> min_communication => TRUE); <a name="16197"></a>END; <a na me="16181"></a>/ <a name="16205"></a> <a name="16208"></a>BEGIN <a name="16213"></a><a href="rarrca29.htm#107166">DBMS_REPCAT.CREATE_ MVIEW_REPOBJECT (</a> <a name="16214"></a> gname => 'hr_repg', <a name="16215"></a> sname => 'hr', <a name="16216"></ a> oname => 'locations_mv1', <a name="16217"></a> type => 'SNAPSHOT', <a name="16221"></a> min_communication =&g t; TRUE); <a name="16222"></a>END; <a name="16206"></a>/ <a name="17556"></a> <a name="17559"></a>BEGIN <a name="17564"></a><a href=" rarrca29.htm#107166">DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (</a> <a name="17565"></a> gname => 'hr_repg', <a name="17566"></a> sname => 'hr', <a name="17567"></a> oname => 'regions_mv1', <a name="17568"></a> type => 'SNAPSHOT', <a name=" 17569"></a> min_communication => TRUE); <a name="17570"></a>END; <a name="17571"></a>/ <a name="16006"></a> <a name="16008">< /a>/* </pre> <a name="11599"></a> <h5 class="LST"><font face="Arial, Helvetica, sans-serif">Step 6 Add objects to the refresh group. </font></h5> <a name="16027"></a> <p class="BP">All of the materialized view group objects that you add to the refresh group are ref reshed at the same time to preserve referential integrity between related materialized views.</p> <pre class="CE"><a name="11535"></a >*/ <a name="16024"></a> <a name="19322"></a>BEGIN <a name="19324"></a><a href="rarrefr2.htm#93895">DBMS_REFRESH.ADD (</a> <a name="1 1537"></a> name => 'mviewadmin.hr_refg', <a name="11538"></a> list => 'hr.countries_mv1', <a name="11539"></a> l ax => TRUE); <a name="11540"></a>END; <a name="11541"></a>/ <a name="11529"></a> <a name="11552"></a>BEGIN <a name="14221"></a><a href="rarrefr2.htm#93895">DBMS_REFRESH.ADD (</a> <a name="13261"></a> name => 'mviewadmin.hr_refg', <a name="13262"></a> list => 'hr.departments_mv1', <a name="13263"></a> lax => TRUE); <a name="11557"></a>END; <a name="11558"></a>/ <a name=" 11546"></a> <a name="11569"></a>BEGIN <a name="14229"></a><a href="rarrefr2.htm#93895">DBMS_REFRESH.ADD (</a> <a name="13273"></a> name => 'mviewadmin.hr_refg', <a name="13274"></a> list => 'hr.employees_mv1', <a name="13275"></a> lax => TRUE ); <a name="11574"></a>END; <a name="11575"></a>/ <a name="11563"></a> <a name="11586"></a>BEGIN <a name="14237"></a><a href="rarrefr 2.htm#93895">DBMS_REFRESH.ADD (</a> <a name="13281"></a> name => 'mviewadmin.hr_refg', <a name="13282"></a> list => ' hr.jobs_mv1', <a name="13283"></a> lax => TRUE); <a name="11591"></a>END; <a name="11592"></a>/ <a name="16428"></a> <a name= "16431"></a>BEGIN <a name="16435"></a><a href="rarrefr2.htm#93895">DBMS_REFRESH.ADD (</a> <a name="16436"></a> name => 'mview admin.hr_refg', <a name="16437"></a> list => 'hr.job_history_mv1', <a name="16438"></a> lax => TRUE); <a name="16439" ></a>END; <a name="16429"></a>/ <a name="16443"></a> <a name="16446"></a>BEGIN <a name="16450"></a><a href="rarrefr2.htm#93895">DBMS_ REFRESH.ADD (</a> <a name="16451"></a> name => 'mviewadmin.hr_refg', <a name="16452"></a> list => 'hr.locations_mv1', <a name="16453"></a> lax => TRUE); <a name="16454"></a>END; <a name="16444"></a>/ <a name="17583"></a> <a name="17586"></a>B EGIN <a name="17590"></a><a href="rarrefr2.htm#93895">DBMS_REFRESH.ADD (</a> <a name="17591"></a> name => 'mviewadmin.hr_refg ', <a name="17592"></a> list => 'hr.regions_mv1', <a name="17593"></a> lax => TRUE); <a name="17594"></a>END; <a name ="17595"></a>/ <a name="17600"></a> <a name="17584"></a>/* </pre> <a name="16591"></a> <h3 class="H2"><font fac e="Arial, Helvetica, sans-serif" color="#330099">Creating the Materialized View Group at mv2.world</font></h3>  <a name ="16592"></a> <p class="BP">Complete the following steps to create the hr_repg materialized view group at the <code>mv2.world</code> materialized view site. This materialized view group is based on the <code>hr_repg</code> materialized view group at the <code>mv1.w orld</code> materialized view site.</p> <a name="16625"></a> <h5 class="LST"><font face="Arial, Helvetica, sans-serif">Step 1 Create materialized view logs at the master materialized view site.</font></h5> <a name="16626"></a> <p class="BP">If you want one of your master materialized view sites to support another materialized view site, then you need to create materialized view logs for each ma terialized view that is replicated to another materialized view site. Recall from <a href="rarrepsi.htm#12531">Figure 2-1</a><a href="rarrepsi.htm#12531"></a> that <code>mv1.world</code> serves as the target master internalized view site for the <code>mv2.world </code> materialized view site. The required materialized view logs must be created at <code>mv1.world</code>.</p> <pre class="CE"><a name="16633"></a>*/ <a name="16634"></a> <a name="16635"></a>CONNECT hr/hr@mv1.world <a name="16636"></a> <a name="16637"></a>CREATE MATERIALIZED VIEW LOG ON hr.countries_mv1; <a name="16638"></a>CREATE MATERIALIZED VIEW LOG ON hr.departments_mv1; <a name="16639">< /a>CREATE MATERIALIZED VIEW LOG ON hr.employees_mv1; <a name="16640"></a>CREATE MATERIALIZED VIEW LOG ON hr.jobs_mv1; <a name="16641" ></a>CREATE MATERIALIZED VIEW LOG ON hr.job_history_mv1; <a name="16642"></a>CREATE MATERIALIZED VIEW LOG ON hr.locations_mv1; <a nam e="17604"></a>CREATE MATERIALIZED VIEW LOG ON hr.regions_mv1; <a name="16643"></a> <a name="16644"></a>/* </pre> <a name="16654"></a > <div align="center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" cellspacing="0" dir="ltr" summary="This is a la yout table to format a note" title="This is a layout table to format a note"> <tr class="NoteAlso"> <td class="NoteAlso"><a name="166 47"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">See Also:</strong></font> <a name="16648"></a> <p class="NB">Th e <code>CREATE</code> <code>MATERIALIZED</code> <code>VIEW</code> <code>LOG</code> statement in the <em class="Italic"><a class="xlin kSRC SQLRF01303" href="../../server$101/b10759/statements_6003.htm#SQLRF01303">Oracle Database SQL Reference</a></em> for detailed in formation about this SQL statement</p></td></tr></table></div> <a name="16657"></a> <h5 class="LST"><font face="Arial, Helvetica, sa ns-serif">Step 2 If they do not already exist, then create the replicated schema and its database link.</font></h5> <a name="17894">< /a> <p class="BP">Before building your materialized view group, you must make sure that the replicated schema exists at the remote m aterialized view site and that the necessary database links have been created.</p> <a name="18944"></a> <p class="BP">For this examp le, if the hr schema does not exist, then create the schema. If the hr schema already exists at the materialized view site, then go t o the next task in this step.</p> <pre class="CE"><a name="16662"></a>*/ <a name="16663"></a> <a name="16664"></a>CONNECT SYSTEM/MANA GER@mv2.world <a name="16666"></a>CREATE TABLESPACE demo_mv2 <a name="16667"></a> DATAFILE 'demo_mv2.dbf' SIZE 10M AUTOEXTEND ON <a n ame="16668"></a> EXTENT MANAGEMENT LOCAL AUTOALLOCATE; <a name="16669"></a> <a name="16670"></a>CREATE TEMPORARY TABLESPACE temp_mv2 <a name="16671"></a> TEMPFILE 'temp_mv2.dbf' SIZE 5M AUTOEXTEND ON; <a name="16672"></a> <a name="16673"></a>CREATE USER hr IDENTIFIE D BY hr; <a name="16674"></a> <a name="16675"></a>ALTER USER hr DEFAULT TABLESPACE demo_mv2 <a name="16676"></a> QUOTA U NLIMITED ON demo_mv2; <a name="16677"></a> <a name="16678"></a>ALTER USER hr TEMPORARY TABLESPACE temp_mv2; <a name="16679"></a> <a n ame="17985"></a>GRANT <a name="17986"></a> CREATE SESSION, <a name="17987"></a> CREATE TABLE, <a name="17988"></a> CREATE PROCE DURE, <a name="17989"></a> CREATE SEQUENCE, <a name="17990"></a> CREATE TRIGGER, <a name="17991"></a> CREATE VIEW, <a name="17 992"></a> CREATE SYNONYM, <a name="17993"></a> ALTER SESSION, <a name="17994"></a> CREATE MATERIALIZED VIEW, <a name="17995"></a> ALTER ANY MATERIALIZED VIEW, <a name="17996"></a> CREATE DATABASE LINK <a name="17997"></a> TO hr; <a name="17983"></a> <a name="1 6691"></a>/* <a name="18581"></a> </pre> <a name="16695"></a> <p class="BP">If it does not already exist, then create the database l ink for the replicated schema.</p> <a name="16696"></a> <p class="BP">Before building your materialized view group, you must make su re that the necessary database links exist for the replicated schema. The owner of the materialized views needs a database link point ing to the <code>proxy_refresher</code> that was created when the master materialized view site was set up.</p> <pre class="CE"><a na me="16697"></a>*/ <a name="16698"></a> <a name="16699"></a>CONNECT hr/hr@mv2.world <a name="16700"></a> <a name="16701"></a>CREATE DA TABASE LINK mv1.world <a name="16702"></a> CONNECT TO proxy_refresher IDENTIFIED BY proxy_refresher; <a name="16703"></a> <a name= "16704"></a>/* </pre> <a name="16715"></a> <div align="center"> <table class="NoteAlso" border="0" width="80%" cellpadding="0" cells pacing="0" dir="ltr" summary="This is a layout table to format a note" title="This is a layout table to format a note"> <tr class="No teAlso"> <td class="NoteAlso"><a name="16707"></a><font face="Arial, Helvetica, sans-serif"><strong class="NH">See Also:</strong></fo nt> <a name="16714"></a> <p class="NB">Step <a href="rarrepsi.htm#19853">6</a><a href="rarrepsi.htm#19853"></a> for more information about creating proxy master materialized view site users</p></td></tr></table></div> <a name="16718"></a> <h5 class="LST"><font fac e="Arial, Helvetica, sans-serif">Step 3 Create the materialized view group.</font></h5> <a name="16719"></a> <p class="BP">The follo wing procedures must be executed by the materialized view administrator at the remote materialized view site.</p> <pre class="CE"><a name="16720"></a>*/ <a name="16721"></a> <a name="16722"></a>CONNECT mviewadmin/mviewadmin@mv2.world <a name="16723"></a> <a name="16 724"></a>/* <a name="16725"></a> </pre> <a name="16726"></a> <p class="BP">The replication group that you specify in the <code>gname </code> parameter must match the name of the replication group that you are replicating at the target master materialized view site.< /p> <pre class="CE"><a name="16727"></a>*/ <a name="16728"></a> <a name="16729"></a>BEGIN <a name="19377"></a><a href="rarrca28.htm#1 06990">DBMS_REPCAT.CREATE_MVIEW_REPGROUP (</a> <a name="16735"></a> gname => 'hr_repg', <a name="16736"></a> master => ; 'mv1.world', <a name="16737"></a> propagation_mode => 'ASYNCHRONOUS'); <a name="16738"></a>END; <a name="16739"></a>/ <a na me="16740"></a> <a name="16741"></a>/* </pre> <a name="16745"></a> <h5 class="LST"><font face="Arial, Helvetica, sans-serif">Step 4 Create the refresh group.</font></h5> <a name="16746"></a> <p class="BP">All materialized views that are added to a particular refre sh group are refreshed at the same time. This ensures transactional consistency between the related materialized views in the refresh group.</p> <pre class="CE"><a name="16747"></a>*/ <a name="16748"></a> <a name="16749"></a>BEGIN <a name="19451"></a><a href="rarref r5.htm#94080">DBMS_REFRESH.MAKE (</a> <a name="16755"></a> name => 'mviewadmin.hr_refg', <a name="16756"></a> list => '', <a name="16757"></a> next_date => SYSDATE, <a name="16758"></a> interval => 'SYSDATE + 1/24', <a name="16759"> </a> implicit_destroy => FALSE, <a name="16760"></a> rollback_seg => '', <a name="16761"></a> push_deferred_rpc => TRUE, <a name="16762"></a> refresh_after_errors => FALSE); <a name="16763"></a>END; <a name="16764"></a>/ <a name="16 765"></a> <a name="16766"></a>/* </pre> <a name="17782"></a> <h5 class="LST"><font face="Arial, Helvetica, sans-serif">Step 5 Add ob jects to the materialized view group.</font></h5> <a name="17874"></a> <p class="BP">Create the materialized views based on the mast er materialized views.</p> <a name="17783"></a> <p class="BP">Whenever you create a materialized view that is based on another mater ialized view, always specify the schema name of the materialized view owner in the query for the materialized view. In the following examples, <code>hr</code> is specified as the owner of the materialized view in each query.</p> <pre class="CE"><a name="17784"></a>* / <a name="17785"></a> <a name="16771"></a>CREATE MATERIALIZED VIEW hr.countries_mv2 <a name="16772"></a> REFRESH FAST WITH PRIMARY KEY FOR UPDATE <a name="16773"></a> AS SELECT * FROM hr.countries_mv1@mv1.world; <a name="16774"></a> <a name="16775"></a>CREATE M ATERIALIZED VIEW hr.departments_mv2 <a name="16776"></a> REFRESH FAST WITH PRIMARY KEY FOR UPDATE <a name="16777"></a> AS SELECT * FROM hr.departments_mv1@mv1.world; <a name="16778"></a> <a name="16779"></a>CREATE MATERIALIZED VIEW hr.employees_mv2 <a name="167 80"></a> REFRESH FAST WITH PRIMARY KEY FOR UPDATE <a name="16781"></a> AS SELECT * FROM hr.employees_mv1@mv1.world; <a name="16782 "></a> <a name="16783"></a>CREATE MATERIALIZED VIEW hr.jobs_mv2 <a name="16784"></a> REFRESH FAST WITH PRIMARY KEY FOR UPDATE <a n ame="16785"></a> AS SELECT * FROM hr.jobs_mv1@mv1.world; <a name="16786"></a> <a name="16787"></a>CREATE MATERIALIZED VIEW hr.job_hi story_mv2 <a name="16788"></a> REFRESH FAST WITH PRIMARY KEY FOR UPDATE <a name="16789"></a> AS SELECT * FROM hr.job_history_mv1@ mv1.world; <a name="16790"></a> <a name="16791"></a>CREATE MATERIALIZED VIEW hr.locations_mv2 <a name="16792"></a> REFRESH FAST WIT H PRIMARY KEY FOR UPDATE <a name="16793"></a> AS SELECT * FROM hr.locations_mv1@mv1.world; <a name="17612"></a> <a name="17615"></a >CREATE MATERIALIZED VIEW hr.regions_mv2 <a name="17616"></a> REFRESH FAST WITH PRIMARY KEY FOR UPDATE <a name="17617"></a> AS SE LECT * FROM hr.regions_mv1@mv1.world; <a name="16794"></a> <a name="16795"></a>/* <a name="18586"></a> </pre> <a name="16799"></a> < p class="BP">Add the materialized views to the materialized view group.</p> <pre class="CE"><a name="16801"></a>*/ <a name="16802"></ a> <a name="16803"></a>BEGIN <a name="16808"></a><a href="rarrca29.htm#107166">DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (</a> <a name="1680 9"></a> gname => 'hr_repg', <a name="16810"></a> sname => 'hr', <a name="16811"></a> oname => 'countries_mv2' , <a name="16812"></a> type => 'SNAPSHOT', <a name="16813"></a> min_communication => TRUE); <a name="16814"></a>END; <a name="16815"></a>/ <a name="16816"></a> <a name="16817"></a>BEGIN <a name="16822"></a><a href="rarrca29.htm#107166">DBMS_REPCAT.CR EATE_MVIEW_REPOBJECT (</a> <a name="16823"></a> gname => 'hr_repg', <a name="16824"></a> sname => 'hr', <a name="1682 5"></a> oname => 'departments_mv2', <a name="16826"></a> type => 'SNAPSHOT', <a name="16827"></a> min_communicat ion => TRUE); <a name="16828"></a>END; <a name="16829"></a>/ <a name="16830"></a> <a name="16831"></a>BEGIN <a name="16836"></a><a href="rarrca29.htm#107166">DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (</a> <a name="16837"></a> gname => 'hr_repg', <a name="16838" ></a> sname => 'hr', <a name="16839"></a> oname => 'employees_mv2', <a name="16840"></a> type => 'SNAPSHOT', <a name="16841"></a> min_communication => TRUE); <a name="16842"></a>END; <a name="16843"></a>/ <a name="16844"></a> <a name= "16845"></a>BEGIN <a name="16850"></a><a href="rarrca29.htm#107166">DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (</a> <a name="16851"></a> gname => 'hr_repg', <a name="16852"></a> sname => 'hr', <a name="16853"></a> oname => 'jobs_mv2', <a name="16854 "></a> type => 'SNAPSHOT', <a name="16855"></a> min_communication => TRUE); <a name="16856"></a>END; <a name="16857"> </a>/ <a name="16858"></a> <a name="16859"></a>BEGIN <a name="16864"></a><a href="rarrca29.htm#107166">DBMS_REPCAT.CREATE_MVIEW_REPOB JECT (</a> <a name="16865"></a> gname => 'hr_repg', <a name="16866"></a> sname => 'hr', <a name="16867"></a> ona me => 'job_history_mv2', <a name="16868"></a> type => 'SNAPSHOT', <a name="16869"></a> min_communication => TRUE); <a name="16870"></a>END; <a name="16871"></a>/ <a name="16872"></a> <a name="16873"></a>BEGIN <a name="16878"></a><a href="rarrca29. htm#107166">DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (</a> <a name="16879"></a> gname => 'hr_repg', <a name="16880"></a> sname => 'hr', <a name="16881"></a> oname => 'locations_mv2', <a name="16882"></a> type => 'SNAPSHOT', <a name="16883"> </a> min_communication => TRUE); <a name="16884"></a>END; <a name="16885"></a>/ <a name="17627"></a> <a name="17630"></a>BEGI N <a name="17635"></a><a href="rarrca29.htm#107166">DBMS_REPCAT.CREATE_MVIEW_REPOBJECT (</a> <a name="17636"></a> gname => 'h r_repg', <a name="17637"></a> sname => 'hr', <a name="17638"></a> oname => 'regions_mv2', <a name="17639"></a> t ype => 'SNAPSHOT', <a name="17640"></a> min_communication => TRUE); <a name="17641"></a>END; <a name="17642"></a>/ <a name ="16886"></a> <a name="16887"></a>/* </pre> <a name="16891"></a> <h5 class="LST"><font face="Arial, Helvetica, sans-serif">Step 6 Ad d objects to the refresh group.</font></h5> <a name="16892"></a> <p class="BP">All of the materialized view group objects that you a dd to the refresh group are refreshed at the same time to preserve referential integrity between related materialized views.</p> <pre class="CE"><a name="16893"></a>*/ <a name="16894"></a> <a name="16895"></a>BEGIN <a name="19460"></a><a href="rarrefr2.htm#93895">DB MS_REFRESH.ADD (</a> <a name="16901"></a> name => 'mviewadmin.hr_refg', <a name="16902"></a> list => 'hr.countries_mv 2', <a name="16903"></a> lax => TRUE); <a name="16904"></a>END; <a name="16905"></a>/ <a name="16906"></a> <a name="16907"></ a>BEGIN <a name="16911"></a><a href="rarrefr2.htm#93895">DBMS_REFRESH.ADD (</a> <a name="16912"></a> name => 'mviewadmin.hr_r efg', <a name="16913"></a> list => 'hr.departments_mv2', <a name="16914"></a> lax => TRUE); <a name="16915"></a>END; <a name="16916"></a>/ <a name="16917"></a> <a name="16918"></a>BEGIN <a name="16922"></a><a href="rarrefr2.htm#93895">DBMS_REFRESH.AD D (</a> <a name="16923"></a> name => 'mviewadmin.hr_refg', <a name="16924"></a> list => 'hr.employees_mv2', <a name=" 16925"></a> lax => TRUE); <a name="16926"></a>END; <a name="16927"></a>/ <a name="16928"></a> <a name="16929"></a>BEGIN <a na me="16933"></a><a href="rarrefr2.htm#93895">DBMS_REFRESH.ADD (</a> <a name="16934"></a> name => 'mviewadmin.hr_refg', <a name ="16935"></a> list => 'hr.jobs_mv2', <a name="16936"></a> lax => TRUE); <a name="16937"></a>END; <a name="16938"></a> / <a name="16939"></a> <a name="16940"></a>BEGIN <a name="16944"></a><a href="rarrefr2.htm#93895">DBMS_REFRESH.ADD (</a> <a name="169 45"></a> name => 'mviewadmin.hr_refg', <a name="16946"></a> list => 'hr.job_history_mv2', <a name="16947"></a> l ax => TRUE); <a name="16948"></a>END; <a name="16949"></a>/ <a name="16950"></a> <a name="16951"></a>BEGIN <a name="16955"></a><a href="rarrefr2.htm#93895">DBMS_REFRESH.ADD (</a> <a name="16956"></a> name => 'mviewadmin.hr_refg', <a name="16957"></a> list => 'hr.locations_mv2', <a name="16958"></a> lax => TRUE); <a name="16959"></a>END; <a name="16589"></a>/ <a name="17 654"></a> <a name="19511"></a>BEGIN <a name="19512"></a><a href="rarrefr2.htm#93895">DBMS_REFRESH.ADD (</a> <a name="19513"></a> name => 'mviewadmin.hr_refg', <a name="17663"></a> list => 'hr.regions_mv2', <a name="17664"></a> lax => TRUE); < a name="17665"></a>END; <a name="17666"></a>/ <a name="18092"></a> <a name="18087"></a>SET ECHO OFF <a name="18088"></a> <a name="180 89"></a>SPOOL OFF <a name="16052"></a> <a name="16043"></a>/************************* END OF SCRIPT ********************************* */ </pre> </div> <div class="footer"> <hr> <table summary="" cellspacing="0" cellpadding="0" width="100%"> <tr> <td style="width: 33% ;" align="left"> <table summary="layout table" cellspacing="0" cellpadding="0" align="left" width="90"> <tr> <td align="center" valig n="top"><a href="rardt.htm"><img src="../../dcommon/gifs/larrow.gif" alt="Go to previous page" border="0"><br> <font size="-2">Previo us</font></a></td> <td align="center" valign="top"><a href="rarconfl.htm"><img src="../../dcommon/gifs/rarrow.gif" alt="Go to next pa ge" border="0"><br> <font size="-2">Next</font></a></td></tr></table></td> <td style="width: 34%;" align="center"><img src="../../dco mmon/gifs/oracle.gif" alt="Oracle"><br> <a href="../../dcommon/html/cpyr.htm"><font size="-2">Copyright © 1996, 2003 Oracle Corp oration</font></a><br> <font size="-2">All Rights Reserved.</font></td> <td style="width: 33%;" align="right"> <table summary="" cell spacing="0" cellpadding="0" width="270"> <tr> <td align="center" valign="top"><a href="../../index.htm"><img src="../../dcommon/gifs/ prodicon.gif" alt="Go to Documentation Home" border="0"><br> <font size="-2">Home</font></a></td> <td align="center" valign="top"><a href="../../nav/portal_3.htm"><img src="../../dcommon/gifs/bookicon.gif" alt="Go to Book List" border="0"><br> <font size="-2">Book L ist</font></a></td> <td align="center" valign="top"><a href="toc.htm"><img src="../../dcommon/gifs/conticon.gif" alt="Go to Table of Contents" border="0"><br> <font size="-2">Contents</font></a></td> <td align="center" valign="top"><a href="index.htm"><img src="../. ./dcommon/gifs/indxicon.gif" alt="Go to Index" border="0"><br> <font size="-2">Index</font></a></td> <td align="center" valign="top"> <a href="../../mix$101/b12039/toc.htm"><img src="../../dcommon/gifs/mix.gif" alt="Go to Master Index" border="0"><br> <font size="-2" >Master Index</font></a></td> <td align="center" valign="top"><a href="../../dcommon/html/feedback.htm"><img src="../../dcommon/ gifs/feedback.gif" alt="Go to Feedback page" border="0"><br> <font size="-2">Feedback</font></a></td></tr></table></td></tr></table>< /div> </body> </html>