My Oracle Support Banner

How to REGISTER and UNREGISTER a Materialized View - Testcase (Doc ID 1393276.1)

Last updated on JUNE 16, 2022

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - 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:

Note.258634.1  Materialized View registration at Primary Site:

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 primary 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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.