This content has been marked as final. Show 6 replies
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.