Home » Server Options » Replication » Materialized Views Refresh in SnapshotGroup
Materialized Views Refresh in SnapshotGroup [message #448556] Wed, 24 March 2010 01:25 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member

Hi All,

How to refresh the dependent materialized views in a snapshot group. It refreshes in the order we add the materialized views to snapshot group?

Here below is the scenario.

MATVW_A

CREATE MATERIALIZED VIEW MATVW_A BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM EMP;

MATVW_B

CREATE MATERIALIZED VIEW MATVW_B BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM MATVW_A;

MATVW_C

CREATE MATERIALIZED VIEW MATVW_C BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM MATVW_B;


I created a snapshotgroup SNGRP with all the above materialized views.

DBMS_REFRESH.MAKE(name => 'SNGRP',
tab => 'MATVW_A,MATVW_B,MATVW_C',
next_date => TRUNC(sysdate) + 1,
interval => 'SYSDATE + 1');

The order of refreshing the materialized views would be like first materialized view MATVW_A should get refresh then after
MATVW_B,MATVW_C.

As they are dependent views.

Is there anyway where we can mention the order of refreshing materialized views in snapshot group.

Any help really appreciated.

Thanks in advance
Re: Materialized Views Refresh in SnapshotGroup [message #448653 is a reply to message #448556] Wed, 24 March 2010 08:44 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Hello Vikram,

You crossed 184 posts. But still you don't know how to post.

http://www.orafaq.com/forum/t/88153/0/

Are you using Advanced Replication or Basic Replication.

If basic replication. I guess you have to schdule one by one.

If it's Advanced Replication. Check Here

-Babu


[Updated on: Wed, 24 March 2010 08:45]

Report message to a moderator

Re: Materialized Views Refresh in SnapshotGroup [message #448655 is a reply to message #448653] Wed, 24 March 2010 08:50 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

one more

Quote:
REFRESH_DEPENDENT Procedure
This procedure refreshes all materialized views that have the following properties:

The materialized view depends on a master table or master materialized view in the list of specified masters.
The materialized view has not been refreshed since the most recent change to a master table or master materialized view on which it depends.
The materialized view and all of the master tables or master materialized views on which it depends are local.
The materialized view is in the view DBA_MVIEWS.
This procedure is intended for use with data warehouses.

Syntax
DBMS_MVIEW.REFRESH_DEPENDENT (
   number_of_failures     OUT    BINARY_INTEGER,
   { list                 IN     VARCHAR2,
   | tab                  IN OUT DBMS_UTILITY.UNCL_ARRAY,}
   method                 IN     VARCHAR2    := NULL,
   rollback_seg           IN     VARCHAR2    := NULL,
   refresh_after_errors   IN     BOOLEAN     := false,
   atomic_refresh         IN     BOOLEAN     := true);


--------------------------------------------------------------------------------
Note:
This procedure is overloaded. The list and tab parameters are mutually exclusive.

--------------------------------------------------------------------------------


http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10802/d_mview.htm#997336

Babu
Previous Topic: Fast Replication is slow
Next Topic: Network Requirements for Oracle Multi Master Replication
Goto Forum:
  


Current Time: Thu Mar 28 16:31:59 CDT 2024