Solved

materialized view refresh

Posted on 2013-10-31
5
404 Views
Last Modified: 2013-11-08
I have a couple of materialized views that i currently refresh on demand.

I need to have them automatically refreshed.

how can i alter these mviews without having to force a complete refresh when i alter?

i have a couple of views that i want to refresh on sundays, a couple that i want to refresh daily.


another question is, can i refresh multiple views at the same time?

say, i have 5, 3 gig tables that i want to refresh at 3am every night.

the source tables are on a mssql server.

will my bottle neck be processing power at the mssql server? or network bandwidth.

thanks
0
Comment
Question by:FutureDBA-
  • 3
  • 2
5 Comments
 

Author Comment

by:FutureDBA-
Comment Utility
anyone?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
You can alter the view and provide a start and next time.

An example of this can be found in the link below:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2664480900346253895

The online docs should also have examples.

>>will my bottle neck be processing power at the mssql server? or network bandwidth.

We cannot answer this.  We don't know your system.

>>anyone?

This does nothing.  There are no automated alerts that go out to Experts that are not already involved in the question by posting this.
0
 

Author Comment

by:FutureDBA-
Comment Utility
We cannot answer this.  We don't know your system. <<

Source server = 4 quad core xeons with 16 gigs of ram.
Target ORacle server = 16 core VM with 32 gigs of ram

I can connect both boxes to a gig-e switch on the networking side.



This does nothing.  There are no automated alerts that go out to Experts that are not already involved in the question by posting this.<<

Apologies
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
>>We cannot answer this.  We don't know your system.

Describing your hardware really doesn't help.  There are way to many factors involved for us to help.  You need to look at the amount of data that will be refreshed across the wire, how fast the disks/servers can process it, etc...

You can create smaller tables and set up some tests and attempt to forecast the final actual numbers.

For example:
Create a table with 100,000 rows, set up the materialized view, perform a percentage of DML that you believe will represent actual changes and time it.

Then create 200,000 rows, time it.

etc...  

Then you can start to build a behavior that may allow you to forecast actual times and possibly predict where you may run into problems.

Just knowing the setup is sort of like asking:
I have a Ferarri with a top speed of 150 MPH.  How long will it take me to drive through Chicago and where will I be slowed down?
0
 

Author Closing Comment

by:FutureDBA-
Comment Utility
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now