not sure how it is known, on master site, about the existence of an materialized view on another site referring some table of a master site. it seems registering is not necessary.
on master site orcl1 : I have a table A, and a materialized view log defined on table A.
on another site orcl2 : have a db link to orcl1 and a fast refreshable materialized view SCOTT.A_MVW (create materialized view A_MVW refresh fast as select * from A@orcl1)
on orcl1 I perform : exec DBMS_MVIEW.UNREGISTER_MVIEW (mviewowner => 'SCOTT' ,mviewname => 'A_MVW' ,mviewsite => 'ORCL2');
as a result the materialized view seems to be successfully unregistered (as it doesn't appear anymore in : select * from DBA_REGISTERED_MVIEWS; )
But, to my surprise, I can still perform fast refreshes on A_MVW, which also remove lines from the materialized view log . so how is site orcl1 still aware about the existence of the materialized view ?
it seems that, registered or not at master site, a materialized view behaves the same...or it doesn't ? where is it stored , on master site, information about materialized views (especially those fast refreshable) that reffer local tables ?
thank you for your reply.
I was expecting that unregistering a mview means deleting all the references to that mview on master site.
basically, what happens if a mview is unregistered ? just a record removed from DBA_REGISTERED_MVIEWS ? but still records in dba_base_table_mviews, which makes fast refreshes of the unregistered materialized view still possible. quite disturbing.
what would be the purpose of unregistering a a mview then ?
The view allows the Administrator at the Master (Source) site to see information about MVs in remote databases that are querying his/her database.
A database in Singapore may be administered by DBA "Hemant" . This database may have built (with appropriate permissions !!!) an MV that queries table(s) in a database in London managed by "Alex". Alex can query DBA_REGISTERED_MVIEWS in his (London) database to see that a remote MV is referencing tables locally.
Why would "Alex" UNREGISTER the MV ? There's no real need to. Unless the information needs to be "hidden" from, say, an Outsource DBA who will be taking "Alex"'s place ! (just kidding !)
Hemant K Chitale
what happens if a materialized view is dropped, and for some reason ( network issues) it is not properly unergistered on the master site ? There will still be references to it in a lot of dictionary views (like DBA_REGISTERED_MVIEWS, dba_base_table_mviews, maybe others as well...).
How to clean up these references on master site if "DBMS_MVIEW.UNREGISTER_MVIEW" cleans up only DBA_REGISTERED_MVIEWS ?
There should be a way to clean up everything, otherwise (in case the mview was a fast refreshable one) the corresponding materialized view log will grow a lot.
We are on Oracle 9i. The remote database has been retired but the master site still retains the logs waiting for the remote materialized view to refresh. While the remote database was retired the materialized view was not dropped.
In this situation, how do we get rid of the views registered on the master site? Executed DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT of the remote snapshot on the master site. A subsequent refresh did not purge the logs used by the remote view.