I'm in trouble due to the growth of MLOG $ _ tables that are created through Materialized Views.
One of these LOG tables is now 45GB. I noticed that at night a maintenance JOB is running that makes an UNDO tablespace stick with 65GB, believing you're trying to maintain that log tables.
I would like to know what I can do with these LOG tables, without causing a problem in the set of MV + Logs. If I can do truncate, I already solve my problem and I perform monthly maintenance on those tables.
What do I do to make the truncate table in the logs? Can not generate a problem?
1 Answer
These MLOG$_ tables are used to store the new records since the last refresh of MV which depends on the table. Once the depending MV is refreshed these records will be deleted.
The size of the UNDO tablespaces increases as the DML activities increases. It also indicates that there is heavy DML operations are going on which has caused the MLOG$_ tables to grow.
The reason could be the large interval of refreshing MVs. So, increase the frequency of refresh on the MVs.
You might be interested in this documentation.
