Alter User: Growing MLOG$ Tables with EBS 12

There are several snapshot tables (materialized views) in EBS that are set to Fast Refresh on Demand that, if not accessed, will grow to very large sizes. It's good to take a look at the what schemas these tables might reside in using the following SQL:

SELECT owner, object_name

FROM dba_objects

WHERE object_name LIKE 'MLOG$%' AND object_type = 'TABLE'

ORDER BY owner, object_name

Added Tip: Or even better, run this to see which MLOG tables you should address first:

SELECT *

FROM ( SELECT owner, table_name, TO_NUMBER (EXTRACTVALUE (xmltype (DBMS_XMLGEN.getxml ('select count(*) c from ' || owner || '.' || table_name)), '/ROWSET/ROW/C')) COUNT

FROM all_tables

WHERE table_name LIKE 'MLOG$%'

ORDER BY owner, table_name)

ORDER BY COUNT DESC

If you stats are current, you can quickly get an idea of the impact of this on your system. In my case, I had several snapshot logs (MLOG$ tables) that were over 1m rows and as high as 17m rows.

Using Toad, it might be as simple as navigating to the underlying materialized view and right-click and doing a Refresh (Complete, Atomic = False). To find out what MV this log is tied to, you can use this:

SELECT a.OWNER,

a.MASTER,

a.MVIEW_LAST_REFRESH_TIME,

m.OWNER,

m.NAME,

m.MVIEW_SITE

FROM all_base_table_mviews a, ALL_REGISTERED_MVIEWS m

WHERE m.MVIEW_ID = a.MVIEW_ID AND a.MASTER = 'MTL_MATERIAL_TRANSACTIONS_TEMP'

To find out what a.MASTER is looking for, simply browse the DDL for the MLOG$ object. You will see a comment made:

COMMENT ON TABLE INV.MLOG$_MTL_MATERIAL_TRANSAC IS 'snapshot log for master table INV.MTL_MATERIAL_TRANSACTIONS_TEMP';

When refreshing, you may encounter the following error(s) however:

BEGIN

DBMS_SNAPSHOT.REFRESH(

LIST => 'INV.MTL_SYS_ITEMS_SN'

,METHOD => 'C'

,PUSH_DEFERRED_RPC => TRUE

,REFRESH_AFTER_ERRORS => FALSE

,PURGE_OPTION => 1

,PARALLELISM => 0

,ATOMIC_REFRESH => FALSE

,NESTED => FALSE);

END;

Error at line 2

ORA-12008: error in materialized view refresh path

ORA-08002: sequence MRP_AP_REFRESH_S.CURRVAL is not yet defined in this session

ORA-06512: at "APPS.MRP_SN_SYS_ITEMS_T1", line 7

ORA-04088: error during execution of trigger 'APPS.MRP_SN_SYS_ITEMS_T1'

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2429

ORA-06512: at line 2

At this point, it is just as easy to drop and recreate the MV. If you had very large MLOG$ tables, you will have to do the following to return space back to the tablespace:

11:24:00 GOLD1> alter materialized view log on INV.MTL_MATERIAL_TRANSACTIONS_TEMP enable row movement;