Help - Search - Members - Calendar
Full Version: Materialized view query
Oracle DBA Forums > Oracle > Oracle Forum
We have a database with master tables and materialized view logs that are used by other databases to refresh fast at specified intervals.

For some reason, records are not flushed from the logs.

We are aware of 4 databases that have Materialized views against this database and have checked the refresh jobs for failures. There have been no failures.

However, at one point in time, we created a small database for testing with materialized views running against the database and later deleted it.

I understand from reading the following article that the master table and logs might not have been aware that the materialized views on this test database were not dropped since a command was not issued to drop them and the database was just dropped.

My question is, How do I query the master database to get a list of Materialized Views that the master tables are expecting to refresh?

Managing Materialized View Log Space
Oracle automatically tracks which rows in a materialized view log have been used during the refreshes of materialized views, and purges these rows from the log so that the log does not grow endlessly. Because multiple simple materialized views can use the same materialized view log, rows already used to refresh one materialized view might still be needed to refresh another materialized view. Oracle does not delete rows from the log until all materialized views have used them.

For example, suppose two materialized views were created against the customers table in a master site. Oracle refreshes the customers materialized view at the spdb1 database. However, the server that manages the master table and associated materialized view log does not purge the materialized view log rows used during the refresh of this materialized view until the customers materialized view at the spdb2 database also refreshes using these rows.

Because Oracle must wait for all dependent materialized views to refresh before purging rows from a materialized view log, unwanted situations can occur that cause a materialized view log to grow indefinitely when multiple materialized views are based on the same master table or master materialized view.

For example, such situations can occur when more than one materialized view is based on a master table or master materialized view and one of the following conditions is true:

One materialized view is not configured for automatic refreshes and has not been manually refreshed for a long time.

One materialized view has an infrequent refresh interval, such as every year (365 days).

A network failure has prevented an automatic refresh of one or more of the materialized views based on the master table or master materialized view.

[b]A network or site failure has prevented a master table or master materialized view from becoming aware that a materialized view has been dropped.[/b]


If you purge or TRUNCATE a materialized view log before a materialized view has refreshed the changes that were deleted, then the materialized view must perform a complete refresh.
Purging Rows from a Materialized View Log
Always try to keep a materialized view log as small as possible to minimize the database space that it uses. To remove rows from a materialized view log and make space for newer log records, you can perform one of the following actions:

Refresh the materialized views associated with the log so that Oracle can purge rows from the materialized view log.

Manually purge records in the log by deleting rows required only by the nth least recently refreshed materialized views.

To manually purge rows from a materialized view log, execute the PURGE_LOG procedure of the DBMS_MVIEW package at the database that contains the log. For example, to purge entries from the materialized view log of the customers table that are necessary only for the least recently refreshed materialized view, execute the following procedure:

master => 'hr.employees',
num => 1,
flag => 'DELETE');

answered this myself
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2016 Invision Power Services, Inc.