Materialized Views - Pro Oracle Database 12c Administration - page 430

In this example, MLOG$_SALES had a problem with purging records because of an associated MV's not successfully
refreshing. This MV log subsequently grew large. The issue was identified and resolved, and now the log's space needs
to be reduced. To shrink the space in an MV log, first enable row movement on the appropriate MV log MLOG$ table:
SQL> alter table mlog$_sales enable row movement;
Next, issue the ALTER MATERIALIZED VIEW LOG ON...SHRINK statement. Note that the table name (after the
keyword ON ) is that of the master table:
SQL> alter materialized view log on sales shrink space;
This statement may take a long time, depending on the amount of space it shrinks. After the statement finishes,
you can disable row movement:
SQL> alter table mlog$_sales disable row movement;
You can verify that the space has been reduced by running the query from the prior section, which selects from
DBA_SEGMENTS .
Checking the Row Count of an MV Log
As mentioned earlier, sometimes there are problems with an MV's refreshing, and this results in the building up of a
large number of rows in the corresponding MV log table. This can happen when multiple MVs are using one MV log,
and one of the MVs can't perform a fast refresh. In this situation the MV log continues to grow until the issue is resolved.
One way of detecting whether an MV log isn't being purged is to check the row counts of the MV log tables
periodically. The following query uses SQL to generate SQL that creates a script that checks row counts for MV log
tables owned by the currently connected user:
set head off pages 0 lines 132 trimspool on
spo mvcount_dyn.sql
select 'select count(*) || ' || '''' || ': ' || table_name || ''''
|| ' from ' || table_name || ';'
from user_tables
where table_name like 'MLOG%';
spo off;
This script generates a script named mvcount_dyn.sql , containing the SQL statements to select row counts from
the MLOG$ tables. When you're inspecting row counts, you must be somewhat familiar with your application and have
an idea of what a normal row count is. Here is some sample code generated by the previous script:
select count(*) || ': MLOG$_SALES' from MLOG$_SALES;
select count(*) || ': MLOG$_REGION' from MLOG$_REGION;
Moving an MV Log
You may need to move an MV log because the initial creation script didn't specify the correct tablespace. A common
scenario is that the tablespace isn't specified, and the MV log is placed by default in a tablespace such as USERS . You
can verify the tablespace information with this query:
select table_name, tablespace_name
from user_tables
where table_name like 'MLOG%';