Re: Big MV

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