How to REGISTER and UNREGISTER a Materialized View - Testcase
(Doc ID 1393276.1)
Last updated on MARCH 12, 2021
Applies to:Oracle Database - Enterprise Edition - Version 126.96.36.199 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A 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:
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.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document