Is my Materialized View Log being used?

We have a large number of production databases that are being used as the source for Materialized Views.

Often the developers just add an MV Log to a new table on the chance that it might be used. As we all know MV Logs or MLogs are used to record changed rows within a table, so that the MV based upon that source table, can perform a fast refresh.

I needed a way to locate all MLOGs and work out if they are still or have even been used. The solution to this is quite simple.

The object SYS.MLOG$ contains various details of the MLOGs usage. The information is better at 11g but ok at 10g.

The following query lists all tables who's name starts with MLOG$, and then added the SYS.MLOG$ details. I've used an outer join to still show tables that have never been used as MV Logs. The v$database is only there because I was doing a lot.

select db.name, tabs.owner, tabs.table_name, mlog.master, mlog.log, mlog.youngest
from sys.mlog$ mlog, v$database db, dba_tables tabs
where tabs.owner = mlog.mowner(+) and tabs.table_name = mlog.log(+)
and tabs.table_name like 'MLOG$%' and tabs.table_name not in ('MLOG$','MLOG_REF$')
order by 2,3;

Let me know what you think of my solution.

Happyjohn