Recently while checking the segment’s size for a particular schema, i noticed a materialized view log (MLOG$_TABLE_NAME) occupying almost 10Gb of space.
What is materialized view log? From the Oracle document :-
When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.
A materialized view log is located in the master database in the same schema as the master table. A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.
Coming back to issue,
17:04:33 SQL> @segment_info Enter value for segment_name: MLOG$_CONS_DET OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB ---------- ------------------------- ------------------ -------------------- ---------- ABC MLOG$_CONS_DET TABLE USER 9868
The mview log is approximately 9.6Gb, which is too big and occupying lots of space.It was time to find out the reason why the mview log was not getting purged??
sql >select LOG_OWNER,MASTER,LOG_TABLE,LOG_TRIGGER,PRIMARY_KEY from dba_mview_logs where log_owner='ABC' and MASTER='CONS_DET' LOG_OWNER MASTER LOG_TABLE LOG_TRIGGER PRI ------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------ ABC CONS_DET MLOG$_CONS_DET YES 17:09:33 SQL> select MOWNER,MASTER,SNAPSHOT,SNAPID,SNAPTIME,SSCN,USER# from sys.slog$ where mowner='ABC' and master='CONS_DET'; MOWNER MASTER SNAPSHOT SNAPID SNAPTIME SSCN USER# ------------------------------ ------------------------------ ----------------- ---------- ----------------- ---------- ---------- ABC CONS_DET 442 13-06-08 00:16:32 ABC CONS_DET 500 06-08-10 00:55:17
The SYS.SLOG$ shows two entries for the master table CONS_DET.Oracle keeps track of snapshots that are using entries in SYS.SLOG$ at the master site. In fact, when deciding whether to purge snapshot log records, Oracle compares SYS.SLOG$.SNAPTIME for the table with MLOG$_.SNAPTIME$$. The rows with a MLOG$_.SNAPTIME$$ equal to or older than the oldest SYS.SLOG$.SNAPTIME for the table are purged from the log. If an orphan entry exists in SYS.SLOG$ at the master site for a deleted snapshot, the SNAPTIME in SLOG$ will not be updated. Consequently, any records in the snapshot log will never be purged during a refresh.
The below query was useful in identifying situations where a snapshot entry exists in SLOG$ but is not registered and has not been updated in a long time.
17:09:46 SQL> @mview_reg_info
Enter value for owner_name: ABC
Enter value for table_name: CONS_DET
SNAPNAME SNAPID SNAPSITE SNAPTIME
------------------------------ ------ ------------------------------ ------------------------------
442 not registered 13-JUN-08 00:16:32
CONS_DET 500 SMI 06-AUG-10 00:55:17
The script used is -
SELECT r.NAME snapname, snapid, NVL(r.snapshot_site, 'not registered') snapsite, snaptime FROM sys.slog$ s, dba_registered_snapshots r
WHERE s.snapid=r.snapshot_id(+) AND mowner LIKE UPPER('&owner') AND MASTER LIKE UPPER('&table_name');
An un-registered snapshot entry in the SYS.SLOG$ for CONS_DET table with last snaptime of “13-06-08 00:16:32” is preventing the mview log from getting purged.
To purge the mview log my plan was :-
1. Drop the mview log on the master table.
2. Re-create the mview log on the master table.
3. Complete refresh of the mview on the destination site.
17:11:09 SQL> DROP MATERIALIZED VIEW LOG ON ABC.CONS_DET; Materialized view log dropped. Elapsed: 00:00:02.28 17:11:16 SQL> @mview_reg_info Enter value for owner_name: ABC Enter value for table_name: CONS_DET no rows selected 17:11:24 SQL> select MOWNER,MASTER,SNAPSHOT,SNAPID,SNAPTIME,SSCN,USER# from sys.slog$ where mowner='ABC' and master='CONS_DET'; no rows selected 17:11:28 SQL> CREATE MATERIALIZED VIEW LOG ON ABC.CONS_DET 17:12:43 2 TABLESPACE USR 17:12:43 3 PCTUSED 30 17:12:43 4 PCTFREE 60 17:12:43 5 INITRANS 20 17:12:43 6 MAXTRANS 255 17:12:43 7 NOCACHE 17:12:43 8 LOGGING 17:12:43 9 NOPARALLEL 17:12:43 10 WITH PRIMARY KEY 17:12:43 11 EXCLUDING NEW VALUES; Materialized view log created. Elapsed: 00:00:00.39 17:12:44 SQL> 17:12:45 SQL> 17:12:45 SQL> 17:12:45 SQL> @mview_reg_info Enter value for owner_name: ABC Enter value for table_name: CONS_DET no rows selected Elapsed: 00:00:00.01 17:12:56 SQL> select MOWNER,MASTER,SNAPSHOT,SNAPID,SNAPTIME,SSCN,USER# from sys.slog$ where mowner='ABC' and master='CONS_DET'; no rows selected Elapsed: 00:00:00.00 17:13:07 SQL> 17:13:53 SQL> @segment_info Enter value for segment_name: MLOG$_CONS_DET OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB ---------- ------------------------- ------------------ -------------------- ---------- ABC MLOG$_CONS_DET TABLE USR .0625
After creating the mview log on the master table (source database), started the complete refresh of the materialized view on target database.
exec dbms_mview.refresh ('CONS_DET','C',parallelism =>4);
During the process checked for the locked objects
18:38:59 SQL> @lock_objects
SID USERNAME OBJECT_OWNER OBJECT_NAME LOCKED_MODE OS_USER_NAME
-------- -------------------- -------------------- ------------------------------ --------------- ------------------------------
351 ABC ABC CONS_DET Row-X (SX) ora
351 ABC SYS MLOG$ Row-X (SX) ora
351 ABC SYS SNAP$ Row-X (SX) ora
351 ABC SYS SNAP_REFOP$ Row-X (SX) ora
SNAP_REFOP$ can be used to check the steps that oracle internally performs to complete the operation.
select VNAME,OPERATION#,SQL_TXT from SNAP_REFOP$ where SOWNER='ABC' and VNAME='CONS_DET';
Once, the complete refresh was over, checked for the entries in SYS.SLOG$
21:17:32 SQL> select MOWNER,MASTER,SNAPSHOT,SNAPID,SNAPTIME,SSCN,USER# from sys.slog$ where mowner='ABC' and master='CONS_DET'; MOWNER MASTER SNAPSHOT SNAPID SNAPTIME SSCN USER# ------------------------------ ------------------------------ --------- ---------- --------- ---------- ---------- ABC CONS_DET 500 06-AUG-10
Learn. Share. Repeat. 
Thanks!
Just what I was looking for, to explain why our development environment has the same issue.
Thanks for visiting the blog and great to know it helped you 🙂
Regards,
Anand
Really useful information. Thank you for the clarity of information provided.
Hello,
very helpful information – and practical explanation how to resolve the issue.
Is there any possibility to check what (program/application) accessing the MLOG tables?
Thanks
Below sql should give you the access details. You can modify as per your need.
select distinct sp.sql_id,
sp.plan_hash_value,
sa.BUFFER_GETS,
sa.EXECUTIONS,
decode (sa.command_type, 2, ‘INSERT’,
3, ‘SELECT’,
6, ‘UPDATE’,
7, ‘DELETE’) COMMAND_NAME,
sa.LAST_ACTIVE_TIME
from v$sql_plan sp, v$sqlarea sa
where sp.sql_id = sa.sql_id and
sp.plan_hash_value = sa.plan_hash_value and
sp.object_name =upper(‘&obj_name’);
Usually MLOG$ is not accessed by any program/application. The dbms_mview refresh access it.
very well explained
Hi ,
one of my MLogs has grown to 4GB and i want to find out who has not consumed their data . Is there any query where i can find that information.
Regards
Sameer
This should help
col snapsite for a35 select s.snapid ,nvl(r.snapshot_site, 'orphan') snapsite ,s.mowner ,s.master ,l.log_table ,s.snaptime ,to_char(s.snaptime, 'mm/dd/yy hh24:mi') snaptimefmt from sys.slog$ s left outer join dba_registered_snapshots r on r.snapshot_id = s.snapid join dba_mview_logs l on s.mowner = l.log_owner and s.master = l.master where (sysdate - s.snaptime) * 86400 > 28800 order by s.snaptime / col owner format A10 col snapshot_id for 999999 col snapshot_site for a20 select * from (select site.SNAPSHOT_ID,site.name,site.owner,site.SNAPSHOT_SITE,logs.CURRENT_SNAPSHOTS,sysdate, round( (sysdate-logs.CURRENT_SNAPSHOTS)*1441 ) "Minutes behind" from dba_registered_snapshots site,dba_snapshot_logs logs where site.snapshot_id=logs.snapshot_id order by 7 desc ) where rownum <21;Hi, can you please share the query in your script “segment_info”.
thanks
Hi Nate,
Below is the sql I used —
col owner for a10 col segment_name for a25 select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,(BYTES/1024/1024)MB from dba_segments where SEGMENT_NAME=UPPER('&segment_name');