Materialized View Log Mlog$_jtf_tasks_b Grew Too Huge. (Doc ID 1268105.1)

Last updated on JANUARY 26, 2014

Applies to:

Oracle Common Applications - Version 11.5.9 to 11.5.10.2 [Release 11.5.9 to 11.5.10]
Information in this document applies to any platform.
Checked for relevance on 07/JAN/2012


Goal

MLOG$_JTF_TASKS_B has grown too huge having 140 million rows and consumed 18GB space. Can it safely truncated ?

Support explained to the customer per development update in <> :

The tables MLOG$_JTF_TASKS_TL and MLOG$_JTF_TASKS_B are Materialized View Log Tables. These are created for supporting refresh of Materialized Views on the tables JTF_TASKS_TL and JTF_TASKS_B respectively. Any action that modifies contents of JTF_TASKS_B and JTF_TASKS_TL, adds an entry in these tables.

The Materialized Views and logs were created as part of ER logged by TeleSales team. Ideally the materialized view logs should be purged if the associated materialized views are used by the Application and if there is a periodic incremental refresh of those views. Customer may not have implemented the TeleSales Module and so the MV logs are getting bloated.

Truncate the tables as follows in test:

1. Take a backup of the tables like below:

CREATE TABLE temp_tasks_tl_mlog AS SELECT * FROM MLOG$_JTF_TASKS_TL;
CREATE TABLE temp_tasks_b_mlogAS SELECT * FROM MLOG$_JTF_TASKS_B;

2. TRUNCATE MLOG$_JTF_TASKS_TL;

Customer prefer to drop the materialized view logs to prevent from the same issue happening again and improve performance. Is it safe as well ?

DROP MATERIALIZED VIEW LOG ON JTF_TASKS_B;
DROP MATERIALIZED VIEW LOG ON JTF_TASKS_TL;

Truncating the materialized view logs does helps for now, but it will not be a permannet solution, because the log table will keep accumulating rows by changes on base tables even after truncated.

No materialized views are based on JTF_TASKS_B nor JTF_TASKS_TL shown as below.

SQL> select count(*) from dba_MVIEW_DETAIL_RELATIONS where DETAILOBJ_NAME in ('JTF_TASKS_B', 'JTF_TASKS_TL');

COUNT(*)

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms