Thursday, March 26, 2009

DBA_MVIEW_REFRESH_TIMES bug in 9i

We set up a materialized view replication for a table from a 9i database to a 10g database. According to the following Oracle doc:

http://download.oracle.com/docs/cd/B10501_01/server.920/a96567/repmview.htm#45084

You can also query the DBA_MVIEW_REFRESH_TIMES view at a master site or master materialized view site to obtain the last refresh times for each materialized view. Administrators can use this information to monitor materialized view activity and coordinate changes to materialized view sites if a master table or master materialized view needs to be dropped, altered, or relocated.

It is clearly said that we can query DBA_MVIEW_REFRESH_TIMES to see the last fresh time of materialized views on the master database. However I saw nothing by querying this viev in our case. I am sure that the materialized view is registed, as comfirmed by the following query:

select owner, name, mview_site, can_use_log, updatable, refresh_method from
dba_registered_mviews;

On the mview site, we can clearly see the last fast refresh time from dba_mviews.

To solve the puzzle, I opened a SR with Oracle. It turns out this is a bug:

There is a known issue in this view DBA_MVIEW_REFRESH_TIMES.
The Note # 290270.1 and Bug # 3557488 describes about this issue.
This issue is fixed in 10g as part of enhancement. So, cannot be back ported directly.

No comments: