Need to rebuild materialized view

I have databases in two locations.
in db 'A' I have a table called PEOPLE
in db 'B' I have a materialized view called PEOPLE which is selecting rows from PEOPLE@'A'

I added a column to the PEOPLE table in db 'A'. I need the materialized view to reckognize this column.

What would be the best approach?

ContestoasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
should be easy with this:
http://www.ss64.com/ora/mview_a.html
ALTER MATERIALIZED VIEW PEOPLE REBUILD COMPILE;

Open in new window

0
Jinesh KamdarCommented:
ALTER MATERIALIZED VIEW people REBUILD COMPILE; errored out for me saying invalid option.

I believe for effecting a change in the columns within the underlying tables / views of an MV, you have to re-create the MV.
0
ContestoasAuthor Commented:
I got an error as well, but I managed to try both rebuild and compile separately and none of them worked anyway.

My databases are linked across a WAN, and with possibly unstable and slow network link between them. Therefore I hoped that there was a better approach to this.?

Is it possible to use dynamic sql inside an MW?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Is it possible to use dynamic sql inside an MW?
no.

>herefore I hoped that there was a better approach to this.?
I found that creating a materialized view on a materialized view could solve the issue, with the following logic:

* MV1 (on db2) is refreshed automatically from db1 to db2
* MV2 is refreshed by a job schedule, based on the fact if MV1 is valid AND if it has rows in it.
that way, MV2 is available with valid data to the user, and refreshing it from MV1 will be much faster

another, even more advanced approach would be this:

* MV1 (on db2) is refreshed automatically from db1 to db2, every second "day"
* MV2 (on db2) is refreshed automatically from db1 to db2, every other second "day"
* MV3 will be refreshed from the most recent of MV1 / MV2, using a job schedule.

a variant, to save the work for MV3 would be this:
instead of refreshing MV1 and MV2 automatically, use a job for that, which will update the oldest/invalid MV of the 2.
that job will then also update a NORMAL view to point on the most recent valid MV, after the MV has been updated.
that last method will be the most efficient AND with almost 0 user interruption to access the data. downside: it requires 2 times the stored as you have 2 MV...

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jinesh KamdarCommented:
>> I got an error as well, but I managed to try both rebuild and compile separately and none of them worked anyway.

That's b'coz u cannot change the underlying structure of an MV with an ALTER MATERIALIZED VIEW command. You will have to use the CREATE OR REPLACE MATERIALIZED VIEW command for that.
Re-create the MV by including the new table-column in the SELECT query.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.