Re: Big MV

   As long as you can keep prebuilt table in sync with source database, right before creating a materialized view log, using any mechanism this method will work. Transportable tablespace is another option too.

Cheers
Riyaj

On Tue, Mar 10, 2009 at 1:07 PM, Sanjay Mishra <smishra_97_at_yahoo.com> wrote:

> Thanks Riyaj. I was not aware of prebuilt option . Let me try this approach
> with small table. Can I use the approach with Hotbackup as the tables
> involved are almos 90% of data and I am creating new database to hold the
> MV. Using Export/import might take long time and so just thinking if the
> same can be used with hotbackup and make it faster.
> Thanks
> Sanjay
> ------------------------------
> *From:* Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
> *To:* smishra_97_at_yahoo.com
> *Cc:* oracle-l_at_freelists.org
> *Sent:* Tuesday, March 10, 2009 12:24:51 PM
> *Subject:* Re: Big MV
>
> Sanjay
> It's been a while I did this. So, test it for smaller table.
>
> 1. export the tables from source.
> 2. Keep track of changes in sources after that current scn. (trigger or
> something else) or keep that table read only.
> 3. compress, copy and Import in to target.
> 4. Make sure all recent changes are captured in this newly imported
> table.
> 5. create materialized view log on source table (if fast refresh).
> 6. create materialized view with prebuilt table <table_name> option in
> target.
>
> If it isn't a fast refreshable then this process is a pain to maintain.
>
> For MV groups, it is straightforward. This is one of many hits:
>
>
> http://www.databasejournal.com/features/oracle/article.php/10893_2200191_2/Manually-Refreshing-Materialized-Views-and-Creating-Refresh-Groups-in-Oracle.htm
>
> --
> Cheers
>
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com
> Specialists in Performance, Recovery and EBS11i
> Blog: http://orainternals.wordpress.com
>
>
> On Tue, Mar 10, 2009 at 10:27 AM, Sanjay Mishra <smishra_97_at_yahoo.com>wrote:
>
>> Hi
>>
>> I had to create 10 MV from Source to Dest Database. Size of 10 Tables on
>> Source is above 200G and so creating the MV over the Network will be big
>> load on the Network as well performance/Speed will not be good. So Is there
>> any alternate process to do this Setup. These MV will be refreshed later
>> only once a Week.
>>
>> Also Can somebody point me to good paper or send the doc to create
>> Snapshot Group and include all MV as part of the group instead of all
>> specifying as individual MV. What are the pros and cons of having them as
>> individual MV vs part of Snapshot group. One of the advantage I can
>> understand is the Dependency but if expert advice has some preference.
>>
>> thanks
>> Sanjay
>>
>>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l