One of my clients alerted me that they had found materialized view logs containing tens of millions of rows and asked me for help in examining the situation, so I set to work right away.
You can’t imagine how many “tricks” I had to use, but if you employ materialized views, you may be suffering the same problem and aren’t even aware of it, so join me on this new adventure!
Background
ACME makes heavy use of materialized views and recently found that in the ZEUS_DB database, the materialized view log for the CUSTOMER table contains about 27 million rows, while the one for the POLICY table has nearly 237 million!
According to their records, these tables are being used by two materialized views, one in the ARES_DB database and the other in HERA_DB; they are fast refreshed every hour; therefore, such a huge amount of entries in the materialized view logs is unexpected. Could this be a bug?
A bit of theory
To enable the FAST refresh mode, you must create a materialized view log for each table to be utilized in the materialized view. These logs are recorded in the mlog$ table.
When a materialized view is created, Oracle records it locally in the snap$ table and also remotely in the reg_snap$ table.
Only with FAST refresh materialized views does Oracle remotely record the materialized view’s dependency with the materialized view log in the slog$ table.
The diagnosis
Situations like this typically occur when there are records in the slog$ table for materialized views that are no longer refreshed or do not exist.
Because Oracle finds these rows, it has to preserve the contents of the materialized view logs for when a fast refresh is requested, which may never occur.
To determine the root cause of the problem, we will rely on the results produced by the following query:
SELECT m.mowner, m.master, m.log,
LEAST(m.oldest,m.oldest_pk) oldest,
s.snapid, s.snaptime,
NVL(r.sowner, '<<NOT') sowner,
NVL(r.snapname, 'REGISTERED') snapname,
NVL(r.snapsite, 'MVIEW>>') snapsite
FROM sys.slog$ s,
sys.reg_snap$ r,
sys.mlog$ m
WHERE s.snapid = r.snapshot_id (+)
AND s.mowner = m.mowner
AND s.master = m.master
AND LEAST(m.oldest,m.oldest_pk) < SYSDATE - 120
ORDER BY m.mowner, m.master, s.snapid;
MOWNER MASTER LOG OLDEST SNAPID SNAPTIME SOWNER SNAPNAME SNAPSITE
------- -------- -------------- ---------- ------ ---------- ------ ----------- --------
UMASTER CUSTOMER MLOG$_CUSTOMER 2018-11-19 3088 2018-11-19 UMVIEW MV_CUSTOMER HERA_DB
UMASTER CUSTOMER MLOG$_CUSTOMER 2018-11-19 3090 2025-07-07 UMVIEW MV_CUSTOMER ARES_DB
UMASTER POLICY MLOG$_POLICY 2023-04-20 4506 2023-04-20 <<NOT REGISTERED MVIEW>>
UMASTER POLICY MLOG$_POLICY 2023-04-20 9928 2025-07-07 UMVIEW MV_POLICY HERA_DBWe can see that Oracle has two materialized views registered for the CUSTOMER table: 3090, which is in the ARES_DB database and called MV_CUSTOMER; and 3088, which is in the HERA_DB database and called MV_CUSTOMER, and hasn’t been updated since November 2018.
Oracle has two materialized views registered for the POLICY table: 9928, which is called MV_POLICY and resides in the HERA_DB database; and 4506, which has not been refreshed since April 2023 and is uncertain where it resides.
With this information, we can conclude that this is not a bug, but rather the result of references to materialized views that no longer exist: 3088 and 4506.
Although the root cause is the same, the procedure to be followed will differ for each master table because they present differentiated challenges, as explained below.
Solution for CUSTOMER
In this first scenario, we have fully identified the materialized view that no longer exists, with SNAPID=3088; the materialized view log contains about 27 million rows, which, while large, is manageable.
This is the most typical scenario, so it is relatively simple to address. Follow these steps:
Check the initial status
We obtain the number of rows in the materialized view log.
SELECT COUNT(*) as n_rows
FROM umaster.mlog$_customer;
N_ROWS
------------
26,942,3911
2
Delete unnecessary rows
BEGIN
DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
mviewowner => 'UMVIEW',
mviewname => 'MV_CUSTOMER',
mviewsite => 'HERA_DB' );
END;
/Verify
We verify that the unused rows in the materialized view log are no longer there.
The ones that remain are those required to refresh the materialized views that already exist.
SELECT COUNT(*) as n_rows
FROM umaster.mlog$_customer;
N_ROWS
------------
14,4543
4
Remove reference to materialized view
We have already eliminated the rows from the materialized view log, and they will not accumulate again, but to maintain everything in order, we must take one more step: delete the entry of the materialized view that no longer exists.
We’ll utilize the UNREGISTER_MVIEW operation from the DBMS_MVIEW package, which requires information about the materialized view that no longer exists.
BEGIN
DBMS_MVIEW.UNREGISTER_MVIEW (
mviewowner => 'UMVIEW',
mviewname => 'MV_CUSTOMER',
mviewsite => 'HERA_DB' );
END;
/
Final checks
We check the slog$ table and see that there are no longer any references to SNAPID=3088.
SELECT snapid, snaptime
FROM sys.slog$
WHERE mowner = 'UMASTER'
AND master = 'CUSTOMER'
ORDER BY snapid;
SNAPID SNAPTIME
------ -------------------
3090 2025-07-07 20:19:495
Solution for POLICY
In this particular scenario, we only know that SNAPID=4506 refers to a no longer existing materialized view, and the materialized view log contains about 237 million rows, which is no longer manageable.
This is the most complex scenario, and to deal with it, we will have to follow a somewhat longer approach that requires planning for execution:
Check the initial status
We obtain the number of rows in the materialized view log.
SELECT COUNT(*) as n_rows
FROM umaster.mlog$_policy;
N_ROWS
------------
236,998,042
1
2
Lock the master table
This is when things become complex.
Although the initial step should be to remove unneeded rows from the materialized view log, with the hundreds of millions of rows, this can take too long or even fail because of a variety of issues.
What we’ll do is truncate the materialized view log, which is an immediate action but takes consideration because it may prevent the materialized views from fast refreshing, forcing a complete refresh, which we want to avoid.
Then, as a first step, we will lock the table to prevent new rows from being added to the materialized view log. Because this may impact applications, it must be performed at a planned and accepted time.
SESS_1> LOCK TABLE umaster.policy
IN EXCLUSIVE MODE;
Table(s) Locked.We must not interrupt this session because it is responsible for keeping the master table locked.
Refrescar las vistas materializadas
From another session, we fast refresh the existing materialized views.
BEGIN
DBMS_MVIEW.REFRESH (
list => 'UMVIEW.MV_POLICY',
method => 'F' );
END;
/3
4
Truncar el log de vista materializada
Once we confirmed that all materialized views have been refreshed, we know that the materialized view log only contains entries associated with materialized views that no longer exist; thus, we can truncate it without consequence.
We must do this in a different session from the one in which the master table is locked.
SESS_2> TRUNCATE TABLE umaster.mlog$_policy;
Table truncated.Delete unnecessary rows
We rely on the PURGE_MVIEW_FROM_LOG procedure from the DBMS_MVIEW package; however, because we have no other data, we will utilize the SNAPID of the no longer existing materialized view to identify it.
BEGIN
DBMS_MVIEW.PURGE_MVIEW_FROM_LOG (
mview_id => 4506 );
END;
/5
6
Liberar bloqueo de la tabla maestra
We release the lock on the master table from the same session as in step 2.
SESS_1> ROLLBACK;
Rollback complete.Final checks
We do a fast refresh to guarantee that no existing materialized views are affected.
BEGIN
DBMS_MVIEW.REFRESH (
list => 'UMVIEW.MV_POLICY',
method => 'F' );
END;
/7
Lessons
Some fast refresh materialized views may be dropped or abandoned, so it is recommended that you examine the number of rows in the materialized view logs as well as the materialized view refresh dates on a regular basis.
If you do this, you will avoid extreme cases like the one I described for the POLICY table, but if you find yourself in one, you will know how to resolve it with confidence and efficiency.
And, given you use materialized views, check out my article
How to refresh materialized views faster in 19c? It will be very helpful to you.
In addition to what has already been said, I recommend that you read the notes below:
| 1031924.6 | Snapshot/MView Log Grows Due To Missing/Invalid Snapshot |
| 1539298.1 | How to Purge a Large MView Log And Avoid Full Refresh |
