Home » Server Options » Replication » Materialized View Issues (Sun Solaris 5.9+Oracle 10.2.0.3)
Materialized View Issues [message #347737] Sat, 13 September 2008 08:00 Go to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Hello,

Having one trouble in my replication environment. I'm having one materialized view which is created refresh type "FORCE".

If i do Fast & Force refresh in my replication environment data's not transfering to materialized view site.

But if i done Complete Refresh then only data's comes in materialized view site.

May i know. Why this things happen?

Master Site: (Dev1 Box)

SQL> select count(*) from AP.AP_INVOICES_ALL;
 
  COUNT(*)
----------
     11255
 
SQL> /
 
  COUNT(*)
----------
     11255



Materialized view (Dev2 Box)
Enter user-name: sys as sysdba
Enter password:
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
 
SQL> select count(*) from AP.S_AP_INVOICES_ALL;
 
  COUNT(*)
----------
     11219 ---------------------> Records Mismatch
 

SQL> exec dbms_mview.refresh('AP.S_AP_INVOICES_ALL','F')
/
 
PL/SQL procedure successfully completed.
 
SQL>
  COUNT(*)
----------
     11219
 
SQL> select count(*) from AP.S_AP_INVOICES_ALL;
 
  COUNT(*)
----------
     11219
 
 
SQL> exec dbms_mview.refresh('AP.S_AP_INVOICES_ALL','F')
/
 
PL/SQL procedure successfully completed.
 
 
SQL> exec dbms_mview.refresh('AP.S_AP_INVOICES_ALL','F');
 
PL/SQL procedure successfully completed.
 
SQL> select count(*) from AP.S_AP_INVOICES_ALL;
 
  COUNT(*)
----------
     11219
 
SQL> exec dbms_mview.refresh('AP.S_AP_INVOICES_ALL');
 
PL/SQL procedure successfully completed.
 
SQL> select count(*) from AP.S_AP_INVOICES_ALL;
 
  COUNT(*)
----------
     11219
 
SQL>  exec dbms_mview.refresh('AP.S_AP_INVOICES_ALL','C');
 
PL/SQL procedure successfully completed.
 
SQL> select count(*) from AP.S_AP_INVOICES_ALL;
 
  COUNT(*)
----------
     11255 ---------------------> Now this issue solved.


Please let me know; what was the problem?

Babu
Re: Materialized View Issues [message #347747 is a reply to message #347737] Sat, 13 September 2008 09:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have you a mview log in your table?
Did it have been created before mview?
Did it have been purged before refresh?

Regards
Michel
Re: Materialized View Issues [message #347748 is a reply to message #347747] Sat, 13 September 2008 10:19 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Quote:
Have you a mview log in your table?
Did it have been created before mview?
Did it have been purged before refresh?


1. Yes.
2. Yes
3. Please explain me this question.

Babu
Re: Materialized View Issues [message #347749 is a reply to message #347748] Sat, 13 September 2008 11:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If mview log has been purged before refresh, using dbms_mview.purge_log or delete, then there is nothing to refresh fast.

Regards
Michel

[Updated on: Sat, 13 September 2008 12:51]

Report message to a moderator

Re: Materialized View Issues [message #347751 is a reply to message #347749] Sat, 13 September 2008 12:26 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member


Thanks for update.

But I have re-created materialized view log before creating materialized view.

This is the reason for this issue?

Babu
Re: Materialized View Issues [message #347821 is a reply to message #347751] Sun, 14 September 2008 06:02 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Mr.Michel,

One more information. My base table don't have primary key so that materialized view log created ROWID option.

Babu
Previous Topic: ORA-23388
Next Topic: Multi Master Replication
Goto Forum:
  


Current Time: Thu Mar 28 13:24:06 CDT 2024