to check MLOG size and maintanance

select count(*) from ont.MLOG$_OE_ORDER_LINES_ALL; -- 16623

select count(*) from wip.MLOG$_WIP_DISCRETE_JOBS; --1730

select count(*) from INV.MLOG$_MTL_DEMAND; --1061

select count(*) from INV.MLOG$_MTL_ITEM_CATEGORIES; --567

select count(*) from INV.MLOG$_MTL_MATERIAL_TRANSAC; --19423

select count(*) from  INV.MLOG$_MTL_ONHAND_QUANTITIE;  --4216

select count(*) from  INV.MLOG$_MTL_RESERVATIONS; --1646

select count(*) from  INV.MLOG$_MTL_SUPPLY; --5075

select count(*) from  INV.MLOG$_MTL_SYSTEM_ITEMS_B;-- 1254

select count(*) from  INV.MLOG$_MTL_TXN_REQUEST_LINE; - 17116

select count(*) from  BOM.MLOG$_BOM_COMPONENTS_B; --7381

select count(*) from  BOM.MLOG$_BOM_OPERATION_RESOUR; --6476

select count(*) from  BOM.MLOG$_BOM_OPERATION_SEQUEN;8197

select count(*) from  BOM.MLOG$_BOM_STRUCTURES_B; --21

select count(*) from  MRP.MLOG$_MRP_SCHEDULE_DATES;--264879

select count(*) from  WIP.MLOG$_WIP_DISCRETE_JOBS; --1731

select count(*) from  WIP.MLOG$_WIP_OPERATIONS; --21829

select count(*) from  WIP.MLOG$_WIP_OPERATION_RESOUR;--16213

select count(*) from  WIP.MLOG$_WIP_REQUIREMENT_OPER;--19922

select count(*) from  ONT.MLOG$_OE_ORDER_LINES_ALL; 16695

select count(*) from   MRP.MLOG$_MRP_SCHEDULE_DATES; --264879

steps:

1. Stop all scheduled Collections
2. drop snapshot APPS.BOM_OPR_RTNS_SN;                                 
drop snapshot APPS.WIP_WOPRS_SN;                                 
drop snapshot APPS.WIP_WREQ_OPRS_SN;                             
drop snapshot APPS.PO_ACCEPTANCES_SN;         
drop snapshot APPS.MTL_DEMAND_SN;            
drop snapshot APPS.MTL_RESERVATIONS_SN;          
drop snapshot APPS.MRP_SCHD_DATES_SN;          
drop snapshot APPS.WIP_DSCR_JOBS_SN;         
drop snapshot APPS.BOM_INV_COMPS_SN;         
drop snapshot APPS.MTL_ITEM_CATS_SN;         
truncate table INV.MLOG$_MTL_DEMAND;
truncate table INV.MLOG$_MTL_ITEM_CATEGORIES;
truncate table INV.MLOG$_MTL_MATERIAL_TRANSAC;
truncate table INV.MLOG$_MTL_ONHAND_QUANTITIE;
truncate table INV.MLOG$_MTL_RESERVATIONS;
truncate table INV.MLOG$_MTL_SUPPLY;
truncate table INV.MLOG$_MTL_SYSTEM_ITEMS_B;
truncate table INV.MLOG$_MTL_TXN_REQUEST_LINE;
truncate table BOM.MLOG$_BOM_COMPONENTS_B;
truncate table BOM.MLOG$_BOM_OPERATION_RESOUR;
truncate table BOM.MLOG$_BOM_OPERATION_SEQUEN;
truncate table BOM.MLOG$_BOM_STRUCTURES_B;
truncate table MRP.MLOG$_MRP_SCHEDULE_DATES;
truncate table WIP.MLOG$_WIP_DISCRETE_JOBS;
truncate table WIP.MLOG$_WIP_OPERATIONS;
truncate table WIP.MLOG$_WIP_OPERATION_RESOUR;
truncate table WIP.MLOG$_WIP_REQUIREMENT_OPER;
truncate table ONT.MLOG$_OE_ORDER_LINES_ALL;
3.Set profile option "MSC:Source Setup Required" to Y at site level (it will re-generate all snapshots)
4. Run "Refresh Collection Snapshots" in Fast Mode
5. If step 4 is correctly completed then STOP
6. Run following script:
select distinct bill_sequence_id from apps.BOM_INV_COMPS_SN
where organization_id = 0
group by bill_sequence_id
7.If script at step 6 retrieves rows then run concurrent to Refresh Snapshot with parameters: BOM_INV_COMPS_SN, Automatic


Once every month is needed to do following:
Truncate Table ONT.MLOG$_OE_ORDER_LINES_ALL
Stop WF Background process
Perform a full refresh of OE_ODR_LINES_SN