Re: Big MV

From: Sanjay Mishra <>
Date: Tue, 10 Mar 2009 11:07:11 -0700 (PDT)
Message-ID: <>

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.


From: Riyaj Shamsudeen <>
Sent: Tuesday, March 10, 2009 12:24:51 PM
Subject: Re: Big MV

   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:


Riyaj Shamsudeen
Principal DBA, 
Ora!nternals -
Specialists in Performance, Recovery and EBS11i

On Tue, Mar 10, 2009 at 10:27 AM, Sanjay Mishra <> wrote:


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.


Received on Tue Mar 10 2009 - 13:07:11 CDT

Original text of this message