Re: Big MV

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Tue, 10 Mar 2009 11:24:51 -0500
Message-ID: <203315c10903100924g12fd75f7tc3af0f8359bb033b_at_mail.gmail.com>



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
Received on Tue Mar 10 2009 - 11:24:51 CDT

Original text of this message