Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Andrew.

Asked: September 19, 2016 - 4:34 pm UTC

Last updated: September 20, 2016 - 5:55 pm UTC

Version: Oracle 11gr2

Viewed 1000+ times

You Asked

Hello Tom,

What are the best practices with MV -

<1>

a) generate MV on the DB that owns base tables - and refresh MV and populate over the DB links to other DB's
or
b) Pull data from the source DB over the DB link and populate the MV from base_table@link_to_remote


<2>
Can MV refresh group work over the DB Link or would this create issues...?


<3>
In order to execute refresh group - should we grant privileges like:
a) FLASHBACK ANY TABLE
and/or
b) EXEC DBMS_FLASHBACK

Thank you
Regards
Andrew

and Connor said...

"best practice" is a terrible term :-) because it suggests there is always the "best" way to do something.

"best" is what works best *for you*.

For example:

My network is slow and unreliable. In that case, I might choose to do - generate MV on the DB that owns base tables - and refresh MV and populate over the DB links to other DB's

versus

The number of changes in my base tables is tiny and rare. In that case, I might choose to - Pull (delta) data from the source DB over the DB link and populate the MV from base_table@link_to_remote


Refresh groups can work over a db link - all they do is give you transactional consistency between a number of refresh objects

To my knowledge, you dont need to explicitly grant flashback style privs for refresh groups (even though it might be using that technology under the covers)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

MV strategy

Andrew, September 21, 2016 - 7:37 am UTC

Thank you for your response.

I had sent this - as we had been facing the issue of MV refresh group NOT doing the job (Force on demand by cron shell script) - but rather executing it on the second try or later even. No idea as to why - as I do not have access privs to the database server, so unable to diagnose.

I am opting for using flashback technology instead of MV now but wanted to understand any/all possible root causes. Some had been listed by Oracle in Replication trouble shooting - again it is difficult to handle without any access to the DB server.

Thanks
Andrew