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

Last updated on NOVEMBER 07, 2023

Applies to:

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:

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

Solution

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