Mat view logs cleanout

Quote from documentation

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.

But what if no mat views references to mat view log? Does oracle fills log table? The answer is YES. Check it

DROP TABLE test_mv PURGE;
CREATE TABLE test_mv(a NUMBER PRIMARY KEY, b NUMBER);

CREATE MATERIALIZED VIEW LOG ON test_mv;

INSERT INTO test_mv VALUES (1,1);
INSERT INTO test_mv VALUES (2,2);

SELECT COUNT(*) FROM mlog$_TEST_MV;

  COUNT(*)
----------
         2

Helpful query to check matview logs subscribers and time of last update was found here

SELECT r.NAME snapname, snapid, NVL(r.snapshot_site, 'not registered') snapsite, snaptime, mowner, MASTER 
FROM sys.slog$ s, dba_registered_snapshots r
WHERE  s.snapid=r.snapshot_id(+);

Let’s check, that Oracle realy delete data from matview logs

DROP MATERIALIZED VIEW mv1;
DROP MATERIALIZED VIEW mv2;
DROP TABLE test_mv;

CREATE TABLE test_mv(a NUMBER PRIMARY KEY, b NUMBER);

CREATE MATERIALIZED VIEW LOG ON test_mv
WITH PRIMARY KEY
EXCLUDING NEW VALUES;


CREATE MATERIALIZED VIEW mv1 
refresh
with primary key
FAST on DEMAND AS
SELECT *
FROM test_mv
/

CREATE MATERIALIZED VIEW mv2
refresh
with primary key
FAST on DEMAND AS
SELECT *
FROM test_mv
/

INSERT INTO test_mv VALUES (1,1);
INSERT INTO test_mv VALUES (2,2);
COMMIT;

PROMPT Before refresh
--SELECT * FROM SYS.SLOG$;
--SELECT * FROM user_registered_snapshots;
SELECT r.NAME snapname, snapid, NVL(r.snapshot_site, 'not registered') snapsite, to_char(snaptime, 'dd.mm.yyyy hh24:mi') snaptime, mowner, MASTER 
FROM sys.slog$ s, user_registered_snapshots r
WHERE  s.snapid=r.snapshot_id(+);
SELECT COUNT(*) FROM mlog$_TEST_MV;

BEGIN 
  dbms_mview.refresh('MV1');
END;
/  

PROMPT After refresh of 1 of 2 MV
SELECT r.NAME snapname, snapid, NVL(r.snapshot_site, 'not registered') snapsite, to_char(snaptime, 'dd.mm.yyyy hh24:mi') snaptime, mowner, MASTER 
FROM sys.slog$ s, user_registered_snapshots r
WHERE  s.snapid=r.snapshot_id(+);
SELECT COUNT(*) FROM mlog$_TEST_MV;

BEGIN 
  dbms_mview.refresh('MV2');
END;
/  

PROMPT After refresh of all MV
SELECT r.NAME snapname, snapid, NVL(r.snapshot_site, 'not registered') snapsite, to_char(snaptime, 'dd.mm.yyyy hh24:mi') snaptime, mowner, MASTER 
FROM sys.slog$ s, user_registered_snapshots r
WHERE  s.snapid=r.snapshot_id(+);
SELECT COUNT(*) FROM mlog$_TEST_MV;

Before refresh
SNAPNAME   SNA SNAPSITE   SNAPTIME         MOWNER     MASTER
---------- --- ---------- ---------------- ---------- ----------
MV1         98 ********* 16.12.2016 15:33 TESTUSER    TEST_MV
MV2         99 ********* 16.12.2016 15:33 TESTUSER    TEST_MV
  COUNT(*)
----------
         2
PL/SQL procedure successfully completed
After refresh of 1 of 2 MV
SNAPNAME   SNA SNAPSITE   SNAPTIME         MOWNER     MASTER
---------- --- ---------- ---------------- ---------- ----------
MV1         98 ********* 16.12.2016 15:33 TESTUSER    TEST_MV
MV2         99 ********* 16.12.2016 15:33 TESTUSER    TEST_MV

  COUNT(*)
----------
         2
PL/SQL procedure successfully completed
After refresh of all MV
SNAPNAME   SNA SNAPSITE   SNAPTIME         MOWNER     MASTER
---------- --- ---------- ---------------- ---------- ----------
MV1         98 ********* 16.12.2016 15:33 TESTUSER    TEST_MV
MV2         99 ********* 16.12.2016 15:33 TESTUSER    TEST_MV

  COUNT(*)
----------
         0

So oracle detects refreshing of all matviews and delete data from log

If we create matview through dblink oracle also keeps track refreshing and clears matview log table.