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

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

Goal

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.

Note:

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.

Solution

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