How to REGISTER and UNREGISTER a Materialized View - Testcase
Last updated on FEBRUARY 02, 2017
Applies to:Oracle Database - Enterprise Edition - Version 220.127.116.11 and later
Information in this document applies to any platform.
This is an example of how to REGISTER and UNREGISTER a Materialized View using the MVIEW related APIs from DBMS_MVIEW.
This example complements the 2 very thorough notes in MyOracle Support which cover the high level requirements and options for registering mviews:
Note.258634.1 Materialized View registration at Master Site:
Note.67371.1 How to Manually Register/Unregister Snapshots:
The example in this article has been given to enable the parameters on the DBMS_MVIEW.REGISTER
package to be understood. Specifically the FLAG and SNAPSHOT_ID.
It is not usually necessary to have to run these procedures, as the building and dropping
of MVIEWs does the automatic registration and un-registration respectively of the mview in question.
However, there are certain cases where the mview can appear in the dictionary view
DBA_REGISTERED_MVIEW. And there is no corresponding entry in DBA_MVIEWs view.
This can occur for the following reasons, although there may be more:
1. The remote mview database has been lost, or is not open, so the mview is not
actively refreshing against the master table.
2. The GLOBAL_NAME, DB_DOMAIN has changed and hence the incorrect mview is registered.
This can occur as we hold the db-name (SITE) in the sys table SYS.SNAP_REG$ which
is the base table for the view DBA_REGISTERED_MVIEWS.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms