SQL Commands

This appendix covers the following topics:

Overview
Partition Table SQL Commands
Snapshot SQL Commands
MLOG SQL Commands
Tablespace SQL Commands

Overview

Many of the VCP collection commands can be run directly using SQL commands within SQL*Plus. They may not work correctly in a SQL Browser. Always use a test instance to check these scripts before using on a live instance. You may have to reformat some of these statements due to the way the browser is displaying them.

Partition Table SQL Commands

SQL #1 -- List Partitioned Tables in the System

This SQL lists the partitioned tables that are in the system.

-- Run this from SQL*Plus  the column HIGH_VALUE is column type LONG and will not format correctly in a spreadsheet
set lines 200
set colsep |
spool SQL-1_137293.1.txt

SELECT 
  table_name,
  partition_name,
  num_rows,
  high_value,
  sample_size,
  last_analyzed,
  global_stats
FROM 
  all_tab_partitions
WHERE 
  table_name like 'MSC%'   
  order by                    substr(partition_name,instr(partition_name,'_',-1,1)+1)
                              -- To check the partition count of each table use order by table_name, partition_name
spool off

SQL #2 -- Show Created Instance Partitions

This SQL shows the instance partitions that have been created.

Free Flag valid values:

1 = Yes

2 = No

select      instance_id,
                 free_flag,
                 creation_date,
                 last_update_date
from        msc_inst_partitions;

SQL #3 -- Show Instance Partitions Created and Assigned to an EBS Source Instance

This SQL shows the instance partitions that have been created and assigned to an EBS source instance, as well as their current status.

select      instance_code,
instance_id,
apps_ver,
a2m_dblink,
m2a_dblink,
st_status
from msc_apps_instances

SQL #4 -- Show Plans Created in the System

This SQL shows the plans that have been created in the system.

Plan_type valid values are:

1 = Manufacturing Plan

2 = Production Plan

3 = Distribution Plan

4 = Inventory Plan

5 = Distribution Plan (DRP)

6 = SNO Schedule

More plan types

7 = Production Schedule (PS)

8 = Service Parts Plan (SPP)

9 = Service Parts IO Plan (SPIO)

101 = Rapid Plan MRP

102 = Rapid Plan MPS

103 = Rapid Plan MPP

select
mp.COMPILE_DESIGNATOR "Plan Name",
mp.PLAN_ID "Plan ID",
mp.SR_INSTANCE_ID "Instance ID",
mtp.ORGANIZATION_CODE "Owning Org",
mp.PLAN_COMPLETION_DATE "Last Run Date",
decode (mp.PLAN_TYPE, 1, 'Manufacturing MRP', 2, 'Production MPS',
                      3, 'Master MPP',4,'IO Plan', 5, 'Distribution DRP' ,
                                                                7, 'PS Production Schedule', 6, 'SNO Schedule',
                      8, 'Service Parts SPP', 9,'Service IO Plan',
                      101,'Rapid Plan MRP',102,'Rapid Plan MPS',103,'Rapid Plan MPP',plan_type)"Plan Type",
decode (md.PRODUCTION,1, 'Yes', 2, 'No', NULL, 'No') "Production Flag",
decode (md.LAUNCH_WORKFLOW_FLAG,1, 'Yes', 2, 'No', NULL, 'No') "Launch Workflow",
decode (md.INVENTORY_ATP_FLAG,1, 'Yes', 2, 'No', NULL, 'No') "ATP Plan",
mp.CURR_START_DATE "Start Date",
mp.CUTOFF_DATE "End Date"
from
msc_designators md,
msc_plans mp,
msc_trading_partners mtp
where
md.designator=mp.compile_designator and
md.sr_instance_id=mp.sr_instance_id and
mtp.sr_instance_id=mp.sr_instance_id and
md.sr_instance_id=mtp.sr_instance_id and
mtp.sr_tp_id=md.organization_id and
mtp.sr_tp_id=mp.organization_id and
mp.organization_id=md.organization_id
and mtp.partner_type = 3
ORDER BY "Plan Name";

SQL #5 -- Show Plans and Associated Plan Partitions

This SQL shows plans and their associated plan partitions.

MSC_PLAN_PARTITIONS shows how planning partitions are being used when the profile MSC: Shared Plan Partition = No

When the profile MSC: Shared Plan Partition = Yes, then NO lines should appear in MSC_PLAN_PARTITIONS. **

Free Flag valid values:

1 = Yes

2 = No

select      plan_id,
            plan_name,
            free_flag,
            partition_number
from        msc_plan_partitions

PLAN_ID	PLAN_NAME	FREE_FLAG	PARTITION_NUMBER
347	DG-APS1	2	347
391	PRN_SCP	1	391
411	411	1	411

The example above shows that:

DG-APS1 is a plan that is being used (free_flag = 2).
PRN_SCP is a plan that has been purged, but a new plan has not been defined that uses that partition_number. The plan name is replaced when a new plan and the plan options have been defined.
Plan name 411 is a new partition created by the Create APS Partitions request and has never been used.
This means that two new plans could be defined in the Names form in ASCP. After the plan options are defined and saved, this table will be updated with the plan name and the free_flag = 2.
If no rows with free_flag = 1 are returned, then you may still create the new plan in the Names form, but when attempting to define and save the plan options, an error will be received similar to 'No free partitions available, Contact the DBA to create partitions'. This is because the Names form only inserts into the table MSC_DESIGNATORS. When plan options are defined, then the process checks MSC_PLAN_PARTITIONS and updates the free_flag and plan_name. The tables inserted/updated are MSC_PLANS, MSC_PLAN_ORGANIZATIONS, MSC_DESGINATORS and MSC_SUBINVENTORIES.
Use the concurrent request Create APS Partitions with parameters inst_partition_count = 0 and plan_partition_count = 1 to create a new plan partition.

SQL #6 -- Delete All Tables Where INSTANCE_ID Column Appears

This SQL deletes all the tables where the INSTANCE_ID column appears.

set heading off
set pagesize 500
spool delete_instance_id.sql

select distinct 'delete from '||TABLE_NAME||' where instance_id = nn;' 
  -- replace nn with your instance_id  
from   dba_tab_columns
where  column_name = 'INSTANCE_ID'
        and owner = 'MSC';
spool off

The script will have lines like the following and nn should have your INSTANCE_ID:

delete from MSC_APPS_INSTANCES where instance_id = nn;
delete from MSC_ATP_SOURCES_TEMP where instance_id = nn;
delete from MSC_COLL_PARAMETERS where instance_id = nn;

Edit the file to remove the SQL statement and the spool off command, and then submit the file in SQL*Plus:

   SQL>@delete_instance_id.sql
   SQL>commit;

SQL #7 -- Delete All Tables Where SR_INSTANCE_ID Column Appears

This SQL deletes all tables where the SR_INSTANCE_ID column appears.

set  heading off
set  pagesize 500
spool    delete_sr_instance_id.sql

select                         distinct 'delete from '||TABLE_NAME||' where sr_instance_id = nn;' 
                              -- replace nn with your instance_id  
from                          dba_tab_columns
where                         column_name = 'SR_INSTANCE_ID'
and owner = 'MSC'
and table_name like 'MSC%';

spool    off
-- The script will have lines like the following and nn should have your instance_id
delete from MSC_ABC_CLASSES where sr_instance_id = nn;
delete from MSC_ALLOCATION_ASSIGNMENTS where sr_instance_id = nn;
delete from MSC_ALLOC_DEMANDS where sr_instance_id = nn;

Edit the file to remove the SQL statement and the spool off command, and then submit the file in SQL*Plus:

   SQL>@delete_sr_instance_id.sql
   SQL>commit;

Note: This script reports an error when attempting to delete from a %MV or %SN table. This error can be ignored.

SQL #8 -- Drop Plan Partition

This SQL drops a plan partition.

declare 
      errbuf varchar2(1000); 
      retcode number ; 
begin /* Dropping a plan partition */ 
    msc_manage_plan_partitions.drop_force_partition(errbuf,retcode,&plan_partition_number,1); 
end ; 
/

SQL #9 -- Drop Instance Partition

This SQL drops an instance partition.

declare 
      errbuf varchar2(1000); 
      retcode number ; 
begin /* Dropping an instance partition */ 
    msc_manage_plan_partitions.drop_force_partition(errbuf,retcode,&instance_partition_number,2); 
end ; 
/

Important: In 12.0, you must delete the instance listed (Advanced Planning Adminstrator responsibility, navigation: Admin / Instances) before using this SQL to handle the new partitioned tables for the MSC_ST% staging tables.

SQL #10 -- Check MRP_AP_APPS_INSTANCES Table on the EBS Source Instance and Delete Line

This SQL script checks the MRP_AP_APPS_INSTANCES table on the EBS source instance and deletes the line from that table.

Note: For upgraded instances, check both tables.

SQL #11 -- Update All Tables Where SR_INSTANCE_ID Column Appears

This SQL script updates all tables where the SR_INSTANCE_ID column appears.

Important: Do not use for 11.5.10 and above.

set heading off
set linesize 200
set pagesize 500
spool update_sr_instance_id.sql

select  distinct 'update '||TABLE_NAME||' set sr_instance_id = where sr_instance_id = ;' 
        -- replace and with your instance_id's as directed    
from   dba_tab_columns
where  column_name = 'SR_INSTANCE_ID'
        and owner = 'MSC';
spool off

In this example, we used 41 and 21 for SR_INSTANCE_IDs. The script will have lines like the following:

update MSC_ABC_CLASSES set sr_instance_id = 41 where sr_instance_id = 21;
update MSC_ALLOCATION_ASSIGNMENTS set sr_instance_id = 41 where sr_instance_id = 21;
update MSC_ALLOC_DEMANDS set sr_instance_id = 41 where sr_instance_id = 21;

Edit the file to remove the SQL statement and the spool off command and then submit the file in SQL*Plus:

   SQL>@update_sr_instance_id.sql
   SQL>commit;

Note: This script reports an error when attempting to update a %MV or %SN table. This error can be ignored.

SQL #12 -- Update All Tables Where INSTANCE_ID Column Appears

This SQL updates all tables where the INSTANCE_ID column appears.

Important: Do not use this script for 11.5.10 and above.

set heading off
set linesize 200
set pagesize 500
spool update_instance_id.sql

select distinct 'update '||TABLE_NAME||' set instance_id = where instance_id = ;' 
        -- replace and with your instance_id's as directed
from   dba_tab_columns
where  column_name = 'INSTANCE_ID'
        and owner = 'MSC';

spool off

In the following example, 41 and 21 were used as INSTANCE_IDs:

update MSC_APPS_INSTANCES set instance_id = 41 where instance_id = 21;
update MSC_ATP_SOURCES_TEMP set instance_id = 41 where instance_id = 21;
update MSC_COLL_PARAMETERS set instance_id = 41 where instance_id = 21;

Edit the file to remove the SQL statement, and the spool off command, and then submit the file in SQL*Plus:

   SQL>@update_instance_id.sql
   SQL>commit;

SQL #13 -- Delete All Tables Where the INSTANCE_ID Column Appears for ODP Data

This SQL script deletes all tables where the INSTANCE_ID column appears. This is used for ODP data.

set heading off
set pagesize 500
spool delete_ODP_instance_id.sql

select distinct 'delete from '||TABLE_NAME||' where instance_id = nn;'
-- replace nn with your instance_id
from dba_tab_columns
where table_name in
(select object_name from all_objects where object_name like 'MSD%'
and object_type like 'TABLE')
and column_name like 'INSTANCE_ID';
spool off

In this example, nn represents your INSTANCE_ID:

delete from MSD_APP_INSTANCE_ORGS where instance_id = nn;
delete from MSD_ITEM_RELATIONSHIPS where instance_id = nn;
delete from MSD_LOCAL_ID_SETUP where instance_id = nn;

Edit the file to remove the SQL statement, and the spool off command, and then submit the file in SQL*Plus:

   SQL>@delete_odp_instance_id.sql
   SQL>commit;

SQL #14 -- Delete All Tables Where SR_INSTANCE_ID Column Appears for ODP Data

This SQL deletes all tables where the SR_INSTANCE_ID column appears. This is used for ODP data.

set heading off
set pagesize 500
spool delete_ODP_sr_instance_id.sql

select distinct 'delete from '||TABLE_NAME||' where sr_instance_id = nn;'
-- replace nn with your instance_id
from dba_tab_columns
where table_name in
(select object_name from all_objects where object_name like 'MSD%'
and object_type like 'TABLE')
and column_name like 'SR_INSTANCE_ID';

spool off

In this example, nn represents your SR_INSTANCE_ID:

delete from MSD_DEMAND_PLANS where sr_instance_id = nn;
delete from MSD_DP_PLANNING_PCT_DENORM where sr_instance_id = nn;
delete from MSD_DP_SCN_ENTRIES_DENORM where sr_instance_id = nn;

Edit the file to remove the SQL statement and the spool off command and then submit the file in SQL*Plus:

   SQL>@delete_odp_sr_instance_id.sql
   SQL>commit;

SQL #14b -- Delete All Tables Where the Instance Column Appears for ODP Data

This SQL script deletes all tables where the instance column appears.

set heading off
set pagesize 500
spool delete_ODP_instance.sql

select distinct 'delete from '||TABLE_NAME||' where instance = nn;'
-- replace nn with your instance_id
from dba_tab_columns
where table_name in
(select object_name from all_objects where object_name like 'MSD%'
and object_type like 'TABLE')
and column_name like 'INSTANCE';

spool off

The script will have lines like the following and nn should have your INSTANCE_ID.

delete from MSD_BACKUP_LEVEL_VALUES where instance = nn;
delete from MSD_BOM_COMPONENTS where instance = nn;
delete from MSD_BOOKING_DATA where instance = nn;

Edit the file to remove the SQL statement and the spool off command and then submit the file in SQL*Plus:

   SQL>@delete_odp_instance.sql
   SQL>commit;

Snapshot SQL Commands

SQL #15 -- Use Base Table to Find All Related Snapshots

This SQL script finds all the snapshots that are using the MLOG table for a single base table. See SQL # 22 -- Show MLOGs and Related Snapshots and Base Tables for Data Collections and Appendix D -- Snapshots for data collection objects..

SELECT DISTINCT
amdr.MVIEW_NAME "Snapshot",
amdr.OWNER "Snapshot Owner",
amdr.DETAILOBJ_NAME "Base Table Name",
amdr.DETAILOBJ_OWNER "Base Table Owner",
log_table mlog$_name
FROM ALL_MVIEW_DETAIL_RELATIONS amdr,
dba_snapshot_logs dsl
WHERE amdr.DETAILOBJ_NAME LIKE '&Base_table_name'
AND amdr.DETAILOBJ_TYPE = 'TABLE'
AND amdr.DETAILOBJ_NAME = dsl.MASTER
order by detailobj_name, mview_name;

SQL #16 -- Show All Information on a Snapshot in DBA_SNAPSHOTS

Review the snapshot information in the database for one particular snapshot including the query used by the snapshot.

Note: The Master column can only show one MLOG$ table, so check the Query column to see which tables are involved. Alternatively, use SQL #18 -- Use Snapshots to Find All Base Tables and MLOGs Used by a Snapshot to see all base tables and MLOGs.

select * from dba_snapshots where name like '&snapshot_name';

SQL #17 -- Show List of Snapshots Used by VCP Data Collections

Different from output of SQL #22 -- Show MLOGs and Related Snapshots and Base Tables for Data Collections and Appendix D -- Snapshots, this SQL script shows only VCP snapshots. Currently all snapshots with name like %SN, except MSC_ATP_PLAN_SN and WSH_DELIVERY_DETAILS_SN, are used for Data Collections. MSC_ATP_PLAN_SN is used by ATP Plans to store record of items in the ATP plan. WSH_DEVLIERY_DETAILS_SN is used by Oracle Transportation Management (OTM).

Note: Master column will show the base table. If it is a complex snapshot, then multiple tables can be used and aren't be shown here. Check the Query column to see if multiple tables are involved for the snapshot. Alternatively, run SQL #18 -- Use Snapshots to Find All Base Tables and MLOGs Used by a Snapshot.

select * from dba_snapshots where name like '%SN'
and name not in ('MSC_ATP_PLAN_SN','WSH_DELIVERY_DETAILS_SN')
order by name;

SQL #18 -- Use Snapshots to Find All Base Tables and MLOGs Used by a Snapshot

Since SQL #17 -- Show List of Snapshots Used by VCP Data Collections output can only show one table in the Master column, you must either examine the Query column or run this SQL to get list of base tables/MLOGs being used for a snapshot.

Note: DERIVED_MLOG$_NAME: If you have a rare 27 character MLOG name, then only 26 characters can be shown in this output and the name may not be correct. Run SQL #15 -- Use Base Table to Find All Related Snapshots using the base table if required.

select OWNER "Snapshot Owner", 
MVIEW_NAME "Snapshot", 
DETAILOBJ_OWNER "Base Table Owner",
DETAILOBJ_NAME "Base Table Name", 
'MLOG$_'||SUBSTR(DETAILOBJ_NAME,1,20) derived_mlog$_name
FROM ALL_MVIEW_DETAIL_RELATIONS
where MVIEW_NAME like '&Snapshot'
and DETAILOBJ_TYPE='TABLE'
order by mview_name,detailobj_name;

MLOG SQL Commands

**SQL #19 -- Get COUNT(*) on MLOGs**

Alternately, you can use SQL #20 -- Gather Table Statistics for All MLOGs Used in Data Collections to gather table statistics and then use SQL #21 -- Size, NUM_ROWS and LAST_ANALYZED for MLOGs Used in Data Collections to get more information. Single example:

select count(*) from &owner_&MLOG_name;
like select count(*) from BOM.MLOG$_BOM_COMPONENTS_B;

When running the full script, ignore ORA-942 errors. The errors mean that your release does not have this MLOG.

set echo on
select count(*) from AHL.MLOG$_AHL_SCHEDULE_MATERIA;
select count(*) from BOM.MLOG$_BOM_COMPONENTS_B;
select count(*) from BOM.MLOG$_BOM_CTO_ORDER_DEMAND;
select count(*) from BOM.MLOG$_BOM_DEPARTMENTS;
select count(*) from BOM.MLOG$_BOM_OPERATIONAL_ROUT;
select count(*) from BOM.MLOG$_BOM_OPERATION_NETWOR;
select count(*) from BOM.MLOG$_BOM_OPERATION_RESOUR;
select count(*) from BOM.MLOG$_BOM_OPERATION_SEQUEN;
select count(*) from BOM.MLOG$_BOM_RESOURCE_CHANGES;
select count(*) from BOM.MLOG$_BOM_RES_INSTANCE_CHA;
select count(*) from BOM.MLOG$_BOM_STRUCTURES_B;
select count(*) from BOM.MLOG$_BOM_SUBSTITUTE_COMPO;
select count(*) from BOM.MLOG$_BOM_SUB_OPERATION_RE;
select count(*) from CSP.MLOG$_CSP_REPAIR_PO_HEADER;
select count(*) from EAM.MLOG$_EAM_WO_RELATIONSHIPS;
select count(*) from MRP.MLOG$_MRP_FORECAST_DATES;
select count(*) from MRP.MLOG$_MRP_FORECAST_DESIGNA;
select count(*) from MRP.MLOG$_MRP_FORECAST_ITEMS;
select count(*) from MRP.MLOG$_MRP_SCHEDULE_DATES;
select count(*) from INV.MLOG$_MTL_DEMAND;
select count(*) from INV.MLOG$_MTL_ITEM_CATEGORIES;
select count(*) from INV.MLOG$_MTL_MATERIAL_TRANSAC;
select count(*) from INV.MLOG$_MTL_ONHAND_QUANTITIE;
select count(*) from INV.MLOG$_MTL_RELATED_ITEMS;
select count(*) from INV.MLOG$_MTL_RESERVATIONS;
select count(*) from INV.MLOG$_MTL_SUPPLY;
select count(*) from INV.MLOG$_MTL_SYSTEM_ITEMS_B;
select count(*) from INV.MLOG$_MTL_TXN_REQUEST_LINE;
select count(*) from INV.MLOG$_MTL_USER_DEMAND;
select count(*) from INV.MLOG$_MTL_USER_SUPPLY;
select count(*) from ONT.MLOG$_OE_ORDER_LINES_ALL;
select count(*) from PO.MLOG$_PO_ACCEPTANCES;
select count(*) from PO.MLOG$_PO_CHANGE_REQUESTS;
select count(*) from PO.MLOG$_PO_SUPPLIER_ITEM_CAP;
select count(*) from WIP.MLOG$_WIP_DISCRETE_JOBS;
select count(*) from WIP.MLOG$_WIP_FLOW_SCHEDULES;
select count(*) from WIP.MLOG$_WIP_LINES;
select count(*) from WIP.MLOG$_WIP_MSC_OPEN_JOB_STA;
select count(*) from WIP.MLOG$_WIP_OPERATIONS;
select count(*) from WIP.MLOG$_WIP_OPERATION_NETWOR;
select count(*) from WIP.MLOG$_WIP_OPERATION_RESOUR;
select count(*) from WIP.MLOG$_WIP_OP_RESOURCE_INST;
select count(*) from WIP.MLOG$_WIP_REPETITIVE_ITEMS;
select count(*) from WIP.MLOG$_WIP_REPETITIVE_SCHED;
select count(*) from WIP.MLOG$_WIP_REQUIREMENT_OPER;
select count(*) from WIP.MLOG$_WIP_SUB_OPERATION_RE;
select count(*) from WSH.MLOG$_WSH_TRIPS;
select count(*) from WSH.MLOG$_WSH_TRIP_STOPS;
select count(*) from WSM.MLOG$_WSM_COPY_OPERATIONS;
select count(*) from WSM.MLOG$_WSM_COPY_OP_NETWORKS;
select count(*) from WSM.MLOG$_WSM_COPY_OP_RESOURCE;
select count(*) from WSM.MLOG$_WSM_COPY_OP_RESOURCE1;
select count(*) from WSM.MLOG$_WSM_COPY_REQUIREMENT;

SQL #20 -- Gather Table Statistics for All MLOGs Used in Data Collections

Gather table statistics on the table, and then check use SQL #21 -- Show Size, NUM_ROWS and LAST_ANALYZED for MLOGs Used in Data Collections to query dba_tables and dba_segments to show info on the MLOGs.

begin FND_STATS.GATHER_TABLE_STATS ('[owner]','[MLOG_NAME]',10); end;

The Gather Table Stats command may error if the object does not exist in your release. You can ignore errors like the following:

ORA-20000: Unable to analyze TABLE "CSP"."MLOG$_CSP_REPAIR_PO_HEADER", 
insufficient privileges or does not exist
ORA-06512: at "APPS.FND_STATS", [error continues]

Here is an example of the full command:

begin FND_STATS.GATHER_TABLE_STATS ('AHL','MLOG$_AHL_SCHEDULE_MATERIA',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('BOM','MLOG$_BOM_COMPONENTS_B',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('BOM','MLOG$_BOM_CTO_ORDER_DEMAND',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('BOM','MLOG$_BOM_DEPARTMENTS',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('BOM','MLOG$_BOM_OPERATIONAL_ROUT',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('BOM','MLOG$_BOM_OPERATION_NETWOR',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('BOM','MLOG$_BOM_OPERATION_RESOUR',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('BOM','MLOG$_BOM_OPERATION_SEQUEN',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('BOM','MLOG$_BOM_RESOURCE_CHANGES',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('BOM','MLOG$_BOM_RES_INSTANCE_CHA',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('BOM','MLOG$_BOM_STRUCTURES_B',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('BOM','MLOG$_BOM_SUBSTITUTE_COMPO',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('BOM','MLOG$_BOM_SUB_OPERATION_RE',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('CSP','MLOG$_CSP_REPAIR_PO_HEADER',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('EAM','MLOG$_EAM_WO_RELATIONSHIPS',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('MRP','MLOG$_MRP_FORECAST_DATES',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('MRP','MLOG$_MRP_FORECAST_DESIGNA',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('MRP','MLOG$_MRP_FORECAST_ITEMS',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('MRP','MLOG$_MRP_SCHEDULE_DATES',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('INV','MLOG$_MTL_DEMAND',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('INV','MLOG$_MTL_ITEM_CATEGORIES',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('INV','MLOG$_MTL_MATERIAL_TRANSAC',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('INV','MLOG$_MTL_ONHAND_QUANTITIE',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('INV','MLOG$_MTL_RELATED_ITEMS',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('INV','MLOG$_MTL_RESERVATIONS',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('INV','MLOG$_MTL_SUPPLY',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('INV','MLOG$_MTL_SYSTEM_ITEMS_B',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('INV','MLOG$_MTL_TXN_REQUEST_LINE',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('INV','MLOG$_MTL_USER_DEMAND',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('INV','MLOG$_MTL_USER_SUPPLY',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('ONT','MLOG$_OE_ORDER_LINES_ALL',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('PO','MLOG$_PO_ACCEPTANCES',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('PO','MLOG$_PO_CHANGE_REQUESTS',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('PO','MLOG$_PO_SUPPLIER_ITEM_CAP',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WIP','MLOG$_WIP_DISCRETE_JOBS',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WIP','MLOG$_WIP_FLOW_SCHEDULES',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WIP','MLOG$_WIP_LINES',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WIP','MLOG$_WIP_MSC_OPEN_JOB_STA',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WIP','MLOG$_WIP_OPERATIONS',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WIP','MLOG$_WIP_OPERATION_NETWOR',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WIP','MLOG$_WIP_OPERATION_RESOUR',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WIP','MLOG$_WIP_OP_RESOURCE_INST',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WIP','MLOG$_WIP_REPETITIVE_ITEMS',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WIP','MLOG$_WIP_REPETITIVE_SCHED',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WIP','MLOG$_WIP_REQUIREMENT_OPER',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WIP','MLOG$_WIP_SUB_OPERATION_RE',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WSH','MLOG$_WSH_TRIPS',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WSH','MLOG$_WSH_TRIP_STOPS',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WSM','MLOG$_WSM_COPY_OPERATIONS',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WSM','MLOG$_WSM_COPY_OP_NETWORKS',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WSM','MLOG$_WSM_COPY_OP_RESOURCE',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WSM','MLOG$_WSM_COPY_OP_RESOURCE1',10);end;
begin FND_STATS.GATHER_TABLE_STATS ('WSM','MLOG$_WSM_COPY_REQUIREMENT',10);end;

SQL #21 -- Show Size, NUM_ROWS and LAST_ANALYZED for MLOGs Used in Data Collections

NUM_ROWS shows count when last analyzed. You must run SQL #20 -- Gather Table Statistics for All MLOGs Used in Data Collections before you run this SQL for accurate results. Size from DBA_SEGMENTS and NUM_ROWS should have some reasonable correlation. If size in megabytes is large but number of rows is small, then this indicates that many deletes have been run and the high water mark is not realistic with respect to the actual size.

Choices to fix this are:

Refresh the snapshot(s) for the MLOG with automatic refresh mode, then gather the table statistics again for this MLOG and check this SQL once more to make sure size is reduced.

OR
Use steps to truncate the MLOG and refresh the snapshot in complete mode, then gather table statisticss again for this MLOG and check this SQL once more to make sure size is reduced.

SELECT dt.owner, dt.table_name, dt.num_rows, dt.last_analyzed, ds.bytes/1024/1024 "Size in MB", dt.tablespace_name, dt.initial_extent, dt.next_extent, ds.extents FROM dba_tables dt, dba_segments ds WHERE table_name=segment_name and table_name IN('MLOG$_AHL_SCHEDULE_MATERIA','MLOG$_BOM_COMPONENTS_B','MLOG$_BOM_CTO_ORDER_DEMAND','MLOG$_BOM_DEPARTMENTS','MLOG$_BOM_OPERATIONAL_ROUT','MLOG$_BOM_OPERATION_NETWOR',
'MLOG$_BOM_OPERATION_RESOUR','MLOG$_BOM_OPERATION_SEQUEN','MLOG$_BOM_RESOURCE_CHANGES','MLOG$_BOM_RES_INSTANCE_CHA','MLOG$_BOM_STRUCTURES_B',
'MLOG$_BOM_SUBSTITUTE_COMPO','MLOG$_BOM_SUB_OPERATION_RE','MLOG$_CSP_REPAIR_PO_HEADER','MLOG$_EAM_WO_RELATIONSHIPS','MLOG$_MRP_FORECAST_DATES',
'MLOG$_MRP_FORECAST_DESIGNA','MLOG$_MRP_FORECAST_ITEMS','MLOG$_MRP_SCHEDULE_DATES','MLOG$_MTL_DEMAND','MLOG$_MTL_ITEM_CATEGORIES','MLOG$_MTL_MATERIAL_TRANSAC',
'MLOG$_MTL_ONHAND_QUANTITIE','MLOG$_MTL_RELATED_ITEMS','MLOG$_MTL_RESERVATIONS','MLOG$_MTL_SUPPLY','MLOG$_MTL_SYSTEM_ITEMS_B','MLOG$_MTL_TXN_REQUEST_LINE',
'MLOG$_MTL_USER_DEMAND','MLOG$_MTL_USER_SUPPLY','MLOG$_OE_ORDER_LINES_ALL','MLOG$_PO_ACCEPTANCES','MLOG$_PO_CHANGE_REQUESTS','MLOG$_PO_SUPPLIER_ITEM_CAP',
'MLOG$_WIP_DISCRETE_JOBS','MLOG$_WIP_FLOW_SCHEDULES','MLOG$_WIP_LINES','MLOG$_WIP_MSC_OPEN_JOB_STA','MLOG$_WIP_OPERATIONS','MLOG$_WIP_OPERATION_NETWOR',
'MLOG$_WIP_OPERATION_RESOUR','MLOG$_WIP_OP_RESOURCE_INST','MLOG$_WIP_REPETITIVE_ITEMS','MLOG$_WIP_REPETITIVE_SCHED','MLOG$_WIP_REQUIREMENT_OPER',
'MLOG$_WIP_SUB_OPERATION_RE','MLOG$_WSH_TRIPS','MLOG$_WSH_TRIP_STOPS','MLOG$_WSM_COPY_OPERATIONS','MLOG$_WSM_COPY_OP_NETWORKS','MLOG$_WSM_COPY_OP_RESOURCE',
'MLOG$_WSM_COPY_OP_RESOURCE1','MLOG$_WSM_COPY_REQUIREMENT')
order by table_name;

SQL #22 -- Show MLOGs and Related Snapshots and Base Tables for Data Collections

Shows Data Collections snapshots AND also the snapshots for other products that share the MLOG tables for Data Collections. Used for the table in Appendix D -- Snapshots.

SORT OPTIONS - default is by Snapshot

OPTION B - sort by MLOG Name, Snapshot

OPTION C - sort by Base Table Name, Snapshot

SELECT DISTINCT
amdr.MVIEW_NAME "Snapshot",
amdr.OWNER "Snapshot Owner",
amdr.DETAILOBJ_NAME "Base Table Name",
amdr.DETAILOBJ_OWNER "Base Table Owner",
log_table mlog$_name
FROM ALL_MVIEW_DETAIL_RELATIONS amdr,
dba_snapshot_logs dsl
where DETAILOBJ_TYPE = 'TABLE'
and (detailobj_owner, detaiLobj_name) not in (('MSC','MSC_COLLECTED_ORGS'))
AND amdr.DETAILOBJ_NAME = dsl.MASTER
and DETAILOBJ_NAME in
('AHL_SCHEDULE_MATERIALS','BOM_COMPONENTS_B','BOM_CTO_ORDER_DEMAND','BOM_DEPARTMENTS','BOM_OPERATIONAL_ROUTINGS','BOM_OPERATION_NETWORKS',
'BOM_OPERATION_RESOURCES','BOM_RESOURCE_CHANGES','BOM_RES_INSTANCE_CHANGES','BOM_STRUCTURES_B','BOM_SUBSTITUTE_COMPONENTS',
'BOM_SUB_OPERATION_RESOURCES','CSP_REPAIR_PO_HEADERS','EAM_WO_RELATIONSHIPS','MRP_FORECAST_DATES','MRP_FORECAST_DESIGNATORS','MRP_FORECAST_ITEMS',
'MRP_SCHEDULE_DATES','MTL_DEMAND','MTL_ITEM_CATEGORIES','MTL_MATERIAL_TRANSACTIONS_TEMP','MTL_ONHAND_QUANTITIES_DETAIL','MTL_RELATED_ITEMS',
'MTL_RESERVATIONS','MTL_SUPPLY','MTL_SYSTEM_ITEMS_B','MTL_TXN_REQUEST_LINES','MTL_USER_DEMAND','MTL_USER_SUPPLY','OE_ORDER_LINES_ALL','PO_ACCEPTANCES',
'PO_CHANGE_REQUESTS','PO_SUPPLIER_ITEM_CAPACITY','WIP_DISCRETE_JOBS','WIP_FLOW_SCHEDULES','WIP_LINES','WIP_MSC_OPEN_JOB_STATUSES','WIP_OPERATIONS',
'WIP_OPERATION_NETWORKS','WIP_OPERATION_RESOURCES','WIP_OP_RESOURCE_INSTANCES','WIP_REPETITIVE_ITEMS','WIP_REPETITIVE_SCHEDULES','WIP_REQUIREMENT_OPERATIONS',
'WIP_SUB_OPERATION_RESOURCES','WSH_DELIVERY_DETAILS','WSH_TRIPS','WSH_TRIP_STOPS','WSM_COPY_OPERATIONS','WSM_COPY_OP_NETWORKS','WSM_COPY_OP_RESOURCES',
'WSM_COPY_OP_RESOURCE_INSTANCES','WSM_COPY_REQUIREMENT_OPS')
order by MVIEW_NAME; -- replace this line to use OPTION B or C
-- order by log_table, mview_name -- OPTION B sort by MLOG Name
-- order by DETAILOBJ_NAME, mview_name -- OPTION C sort by Base Table Name

SQL #23 -- Show All MLOGs with Size Larger Than 10 MB

Takes a baseline reading of the MLOGs on the system. This can be used for all MLOGs on the system larger than 10 MB, not just entities used for Data Collections. Oracle suggests that any MLOGs smaller than 10 MB can be ignored.

SELECT DBA.OWNER "Owner",
dba.segment_name "MLOG$ Name",
dba.bytes/1024/1024 "MLOG Size in MB",
log.master "Base Table Name",
fav.application_name "Application Name"
FROM all_snapshot_logs log,
dba_segments DBA,
fnd_application_vl fav
WHERE dba.segment_name LIKE 'MLOG$%'
AND dba.segment_name=log.log_table
AND dba.bytes>10000000
AND dba.owner=fav.application_short_name
ORDER BY bytes DESC;

SQL #24 -- Truncate all MLOGs Used for Data Collections

After this SQL is run, you must run Refresh Collection Snapshots as a standalone request using complete refresh for all snapshots. If the table does not exist in your release, then that statement will fail. This is not a problem and can be ignored

TRUNCATE TABLE AHL.MLOG$_AHL_SCHEDULE_MATERIA;
TRUNCATE TABLE BOM.MLOG$_BOM_COMPONENTS_B;
TRUNCATE TABLE BOM.MLOG$_BOM_CTO_ORDER_DEMAND;
TRUNCATE TABLE BOM.MLOG$_BOM_DEPARTMENTS;
TRUNCATE TABLE BOM.MLOG$_BOM_OPERATIONAL_ROUT;
TRUNCATE TABLE BOM.MLOG$_BOM_OPERATION_NETWOR;
TRUNCATE TABLE BOM.MLOG$_BOM_OPERATION_RESOUR;
TRUNCATE TABLE BOM.MLOG$_BOM_OPERATION_SEQUEN;
TRUNCATE TABLE BOM.MLOG$_BOM_RESOURCE_CHANGES;
TRUNCATE TABLE BOM.MLOG$_BOM_RES_INSTANCE_CHA;
TRUNCATE TABLE BOM.MLOG$_BOM_STRUCTURES_B;
TRUNCATE TABLE BOM.MLOG$_BOM_SUBSTITUTE_COMPO;
TRUNCATE TABLE BOM.MLOG$_BOM_SUB_OPERATION_RE;
TRUNCATE TABLE CSP.MLOG$_CSP_REPAIR_PO_HEADER;
TRUNCATE TABLE EAM.MLOG$_EAM_WO_RELATIONSHIPS;
TRUNCATE TABLE MRP.MLOG$_MRP_FORECAST_DATES;
TRUNCATE TABLE MRP.MLOG$_MRP_FORECAST_DESIGNA;
TRUNCATE TABLE MRP.MLOG$_MRP_FORECAST_ITEMS;
TRUNCATE TABLE MRP.MLOG$_MRP_SCHEDULE_DATES;
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_RELATED_ITEMS;
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 INV.MLOG$_MTL_USER_DEMAND;
TRUNCATE TABLE INV.MLOG$_MTL_USER_SUPPLY;
TRUNCATE TABLE ONT.MLOG$_OE_ORDER_LINES_ALL;
TRUNCATE TABLE PO.MLOG$_PO_ACCEPTANCES;
TRUNCATE TABLE PO.MLOG$_PO_CHANGE_REQUESTS;
TRUNCATE TABLE PO.MLOG$_PO_SUPPLIER_ITEM_CAP;
TRUNCATE TABLE WIP.MLOG$_WIP_DISCRETE_JOBS;
TRUNCATE TABLE WIP.MLOG$_WIP_FLOW_SCHEDULES;
TRUNCATE TABLE WIP.MLOG$_WIP_LINES;
TRUNCATE TABLE WIP.MLOG$_WIP_MSC_OPEN_JOB_STA;
TRUNCATE TABLE WIP.MLOG$_WIP_OPERATIONS;
TRUNCATE TABLE WIP.MLOG$_WIP_OPERATION_NETWOR;
TRUNCATE TABLE WIP.MLOG$_WIP_OPERATION_RESOUR;
TRUNCATE TABLE WIP.MLOG$_WIP_OP_RESOURCE_INST;
TRUNCATE TABLE WIP.MLOG$_WIP_REPETITIVE_ITEMS;
TRUNCATE TABLE WIP.MLOG$_WIP_REPETITIVE_SCHED;
TRUNCATE TABLE WIP.MLOG$_WIP_REQUIREMENT_OPER;
TRUNCATE TABLE WIP.MLOG$_WIP_SUB_OPERATION_RE;
TRUNCATE TABLE WSH.MLOG$_WSH_TRIPS;
TRUNCATE TABLE WSH.MLOG$_WSH_TRIP_STOPS;
TRUNCATE TABLE WSM.MLOG$_WSM_COPY_OPERATIONS;
TRUNCATE TABLE WSM.MLOG$_WSM_COPY_OP_NETWORKS;
TRUNCATE TABLE WSM.MLOG$_WSM_COPY_OP_RESOURCE;
TRUNCATE TABLE WSM.MLOG$_WSM_COPY_OP_RESOURCE1;
TRUNCATE TABLE WSM.MLOG$_WSM_COPY_REQUIREMENT;

SQL #25 -- Lock Statistics for All MLOGs Used for Data Collections

If you run SQL #24 -- Truncate all MLOGs Used for Data Collections to truncate, then run SQL #20 -- Gather Table Statistics for All MLOGs Used in Data Collections to gather the table statistics. Then use this SQL to lock the stats.

The required complete refresh of the snapshots can be run before or after these steps are completed, but if automated processes or users are working on the system, then MLOGs may start to accumulate rows.

EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('AHL','MLOG$_AHL_SCHEDULE_MATERIA';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('BOM','MLOG$_BOM_COMPONENTS_B';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('BOM','MLOG$_BOM_CTO_ORDER_DEMAND';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('BOM','MLOG$_BOM_DEPARTMENTS';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('BOM','MLOG$_BOM_OPERATIONAL_ROUT';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('BOM','MLOG$_BOM_OPERATION_NETWOR';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('BOM','MLOG$_BOM_OPERATION_RESOUR';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('BOM','MLOG$_BOM_OPERATION_SEQUEN';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('BOM','MLOG$_BOM_RESOURCE_CHANGES';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('BOM','MLOG$_BOM_RES_INSTANCE_CHA';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('BOM','MLOG$_BOM_STRUCTURES_B';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('BOM','MLOG$_BOM_SUBSTITUTE_COMPO';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('BOM','MLOG$_BOM_SUB_OPERATION_RE';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('CSP','MLOG$_CSP_REPAIR_PO_HEADER';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('EAM','MLOG$_EAM_WO_RELATIONSHIPS';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('MRP','MLOG$_MRP_FORECAST_DATES';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('MRP','MLOG$_MRP_FORECAST_DESIGNA';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('MRP','MLOG$_MRP_FORECAST_ITEMS';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('MRP','MLOG$_MRP_SCHEDULE_DATES';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('INV','MLOG$_MTL_DEMAND';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('INV','MLOG$_MTL_ITEM_CATEGORIES';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('INV','MLOG$_MTL_MATERIAL_TRANSAC';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('INV','MLOG$_MTL_ONHAND_QUANTITIE';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('INV','MLOG$_MTL_RELATED_ITEMS';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('INV','MLOG$_MTL_RESERVATIONS';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('INV','MLOG$_MTL_SUPPLY';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('INV','MLOG$_MTL_SYSTEM_ITEMS_B';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('INV','MLOG$_MTL_TXN_REQUEST_LINE';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('INV','MLOG$_MTL_USER_DEMAND';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('INV','MLOG$_MTL_USER_SUPPLY';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('ONT','MLOG$_OE_ORDER_LINES_ALL';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('PO','MLOG$_PO_ACCEPTANCES';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('PO','MLOG$_PO_CHANGE_REQUESTS';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('PO','MLOG$_PO_SUPPLIER_ITEM_CAP';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WIP','MLOG$_WIP_DISCRETE_JOBS';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WIP','MLOG$_WIP_FLOW_SCHEDULES';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WIP','MLOG$_WIP_LINES';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WIP','MLOG$_WIP_MSC_OPEN_JOB_STA';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WIP','MLOG$_WIP_OPERATIONS';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WIP','MLOG$_WIP_OPERATION_NETWOR';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WIP','MLOG$_WIP_OPERATION_RESOUR';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WIP','MLOG$_WIP_OP_RESOURCE_INST';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WIP','MLOG$_WIP_REPETITIVE_ITEMS';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WIP','MLOG$_WIP_REPETITIVE_SCHED';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WIP','MLOG$_WIP_REQUIREMENT_OPER';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WIP','MLOG$_WIP_SUB_OPERATION_RE';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WSH','MLOG$_WSH_TRIPS';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WSH','MLOG$_WSH_TRIP_STOPS';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WSM','MLOG$_WSM_COPY_OPERATIONS';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WSM','MLOG$_WSM_COPY_OP_NETWORKS';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WSM','MLOG$_WSM_COPY_OP_RESOURCE';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WSM','MLOG$_WSM_COPY_OP_RESOURCE1';
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('WSM','MLOG$_WSM_COPY_REQUIREMENT';

SQL#26 -- Create Temporary Tables to Analyze Changes in Snapshot Logs

drop table rcs_analysis_11i_temp;

drop table rcs_analysis_r12_temp;

create table rcs_analysis_11i_temp
(script_date            DATE,
 bom_structures_b       NUMBER,
 bom_cto_order_demand   NUMBER,
 bom_components_b       NUMBER,
 bom_operational_rout   NUMBER,
 bom_operation_networ   NUMBER,
 bom_operation_resour   NUMBER,
 bom_operation_sequen   NUMBER,
 bom_resource_changes   NUMBER,
 bom_substitute_compo   NUMBER,
 bom_sub_operation_re   NUMBER,
 mtl_demand             NUMBER,
 mtl_material_transac   NUMBER,
 mtl_onhand_quantitie   NUMBER,
 mtl_reservations       NUMBER,
 mtl_supply             NUMBER,
 mtl_system_items_b     NUMBER,
 mtl_txn_request_line   NUMBER,
 mtl_user_demand        NUMBER,
 mtl_user_supply        NUMBER,
 mrp_forecast_dates     NUMBER,
 mrp_forecast_designa   NUMBER,
 mrp_forecast_items     NUMBER,
 mrp_schedule_dates     NUMBER,
 oe_order_lines_all     NUMBER,
 po_acceptances         NUMBER,
 po_change_requests     NUMBER,
 po_supplier_item_cap   NUMBER,
 wip_discrete_jobs      NUMBER,
 wip_flow_schedules     NUMBER,
 wip_lines              NUMBER,
 wip_operations         NUMBER,
 wip_operation_resour   NUMBER,
 wip_repetitive_items   NUMBER,
 wip_repetitive_sched   NUMBER,
 wip_requirement_oper   NUMBER);
    
  
create table rcs_analysis_r12_temp
(script_date            DATE,
 ahl_schedule_materia   NUMBER,
 bom_operation_sequen   NUMBER,
 wip_operation_resour   NUMBER,
 wsm_copy_op_networks   NUMBER,
 wsm_copy_op_resource   NUMBER,
 wsm_copy_op_resource1  NUMBER,
 po_change_requests     NUMBER,
 wip_operations         NUMBER,
 wip_op_resource_inst   NUMBER,
 wip_repetitive_sched   NUMBER,
 bom_structures_b       NUMBER,
 mtl_material_transac   NUMBER,
 mtl_system_items_b     NUMBER,
 mtl_user_demand        NUMBER,
 mtl_user_supply        NUMBER,
 mrp_forecast_items     NUMBER,
 po_acceptances         NUMBER,
 wip_requirement_oper   NUMBER,
 wip_sub_operation_re   NUMBER,
 bom_operation_resour   NUMBER,
 bom_resource_changes   NUMBER,
 bom_sub_operation_re   NUMBER,
 mtl_item_categories    NUMBER,
 mrp_forecast_designa   NUMBER,
 wip_flow_schedules     NUMBER,
 wip_repetitive_items   NUMBER,
 wsm_copy_operations    NUMBER,
 bom_components_b       NUMBER,
 mrp_forecast_dates     NUMBER,
 mrp_schedule_dates     NUMBER,
 oe_order_lines_all     NUMBER,
 wip_lines              NUMBER,
 bom_operation_networ   NUMBER,
 mtl_demand             NUMBER,
 mtl_onhand_quantitie   NUMBER,
 po_supplier_item_cap   NUMBER,
 wip_discrete_jobs      NUMBER,
 bom_operational_rout   NUMBER,
 bom_substitute_compo   NUMBER,
 eam_wo_relationships   NUMBER,
 mtl_reservations       NUMBER,
 mtl_supply             NUMBER,
 bom_res_instance_cha   NUMBER,
 mtl_txn_request_line   NUMBER,
 wip_operation_networ   NUMBER,
 wsh_trips              NUMBER,
 wsh_trip_stops         NUMBER,
 wsm_copy_requirement   NUMBER);

SQL #27 -- Check MLOG$ Tables to Get Row Counts

DECLARE

-- R12 Count Variables
   v_r12_ahl_schedule_materia    NUMBER;
   v_r12_bom_operation_sequen    NUMBER;
   v_r12_wip_operation_resour    NUMBER;
   v_r12_wsm_copy_op_networks    NUMBER;
   v_r12_wsm_copy_op_resource    NUMBER;
   v_r12_wsm_copy_op_resource1   NUMBER;
   v_r12_po_change_requests      NUMBER;
   v_r12_wip_operations          NUMBER;
   v_r12_wip_op_resource_inst    NUMBER;
   v_r12_wip_repetitive_sched    NUMBER;
   v_r12_bom_structures_b        NUMBER;
   v_r12_mtl_material_transac    NUMBER;
   v_r12_mtl_system_items_b      NUMBER;
   v_r12_mtl_user_demand         NUMBER;
   v_r12_mtl_user_supply         NUMBER;
   v_r12_mrp_forecast_items      NUMBER;
   v_r12_po_acceptances          NUMBER;
   v_r12_wip_requirement_oper    NUMBER;
   v_r12_wip_sub_operation_re    NUMBER;
   v_r12_bom_operation_resour    NUMBER;
   v_r12_bom_resource_changes    NUMBER;
   v_r12_bom_sub_operation_re    NUMBER;
   v_r12_mtl_item_categories     NUMBER;
   v_r12_mrp_forecast_designa    NUMBER;
   v_r12_wip_flow_schedules      NUMBER;
   v_r12_wip_repetitive_items    NUMBER;
   v_r12_wsm_copy_operations     NUMBER;
   v_r12_bom_components_b        NUMBER;
   v_r12_mrp_forecast_dates      NUMBER;
   v_r12_mrp_schedule_dates      NUMBER;
   v_r12_oe_order_lines_all      NUMBER;
   v_r12_wip_lines               NUMBER;
   v_r12_bom_operation_networ    NUMBER;
   v_r12_mtl_demand              NUMBER;
   v_r12_mtl_onhand_quantitie    NUMBER;
   v_r12_po_supplier_item_cap    NUMBER;
   v_r12_wip_discrete_jobs       NUMBER;
   v_r12_bom_operational_rout    NUMBER;
   v_r12_bom_substitute_compo    NUMBER;
   v_r12_eam_wo_relationships    NUMBER;
   v_r12_mtl_reservations        NUMBER;
   v_r12_mtl_supply              NUMBER;
   v_r12_bom_res_instance_cha    NUMBER;
   v_r12_mtl_txn_request_line    NUMBER;
   v_r12_wip_operation_networ    NUMBER;
   v_r12_wsh_trips               NUMBER;
   v_r12_wsh_trip_stops          NUMBER;
   v_r12_wsm_copy_requirement    NUMBER;

-- Other Variables   
   v_script_date                 DATE;
   
BEGIN

   SELECT sysdate  INTO v_script_date from dual;
   SELECT count(*) INTO v_r12_ahl_schedule_materia  FROM ahl.mlog$_ahl_schedule_materia;
   SELECT count(*) INTO v_r12_bom_operation_sequen  FROM bom.mlog$_bom_operation_sequen;
   SELECT count(*) INTO v_r12_wip_operation_resour  FROM wip.mlog$_wip_operation_resour;
   SELECT count(*) INTO v_r12_wsm_copy_op_networks  FROM wsm.mlog$_wsm_copy_op_networks;
   SELECT count(*) INTO v_r12_wsm_copy_op_resource  FROM wsm.mlog$_wsm_copy_op_resource;
   SELECT count(*) INTO v_r12_wsm_copy_op_resource1 FROM wsm.mlog$_wsm_copy_op_resource1;
   SELECT count(*) INTO v_r12_po_change_requests    FROM po.mlog$_po_change_requests;
   SELECT count(*) INTO v_r12_wip_operations        FROM wip.mlog$_wip_operations;
   SELECT count(*) INTO v_r12_wip_op_resource_inst  FROM wip.mlog$_wip_op_resource_inst;
   SELECT count(*) INTO v_r12_wip_repetitive_sched  FROM wip.mlog$_wip_repetitive_sched;
   SELECT count(*) INTO v_r12_bom_structures_b      FROM bom.mlog$_bom_structures_b;
   SELECT count(*) INTO v_r12_mtl_material_transac  FROM inv.mlog$_mtl_material_transac;
   SELECT count(*) INTO v_r12_mtl_system_items_b    FROM inv.mlog$_mtl_system_items_b;
   SELECT count(*) INTO v_r12_mtl_user_demand       FROM inv.mlog$_mtl_user_demand;
   SELECT count(*) INTO v_r12_mtl_user_supply       FROM inv.mlog$_mtl_user_supply;
   SELECT count(*) INTO v_r12_mrp_forecast_items    FROM mrp.mlog$_mrp_forecast_items;
   SELECT count(*) INTO v_r12_po_acceptances        FROM po.mlog$_po_acceptances;
   SELECT count(*) INTO v_r12_wip_requirement_oper  FROM wip.mlog$_wip_requirement_oper;
   SELECT count(*) INTO v_r12_wip_sub_operation_re  FROM wip.mlog$_wip_sub_operation_re;
   SELECT count(*) INTO v_r12_bom_operation_resour  FROM bom.mlog$_bom_operation_resour;
   SELECT count(*) INTO v_r12_bom_resource_changes  FROM bom.mlog$_bom_resource_changes;
   SELECT count(*) INTO v_r12_bom_sub_operation_re  FROM bom.mlog$_bom_sub_operation_re;
   SELECT count(*) INTO v_r12_mtl_item_categories   FROM inv.mlog$_mtl_item_categories;
   SELECT count(*) INTO v_r12_mrp_forecast_designa  FROM mrp.mlog$_mrp_forecast_designa;
   SELECT count(*) INTO v_r12_wip_flow_schedules    FROM wip.mlog$_wip_flow_schedules;
   SELECT count(*) INTO v_r12_wip_repetitive_items  FROM wip.mlog$_wip_repetitive_items;
   SELECT count(*) INTO v_r12_wsm_copy_operations   FROM wsm.mlog$_wsm_copy_operations;
   SELECT count(*) INTO v_r12_bom_components_b      FROM bom.mlog$_bom_components_b;
   SELECT count(*) INTO v_r12_mrp_forecast_dates    FROM mrp.mlog$_mrp_forecast_dates;
   SELECT count(*) INTO v_r12_mrp_schedule_dates    FROM mrp.mlog$_mrp_schedule_dates;
   SELECT count(*) INTO v_r12_oe_order_lines_all    FROM ont.mlog$_oe_order_lines_all;
   SELECT count(*) INTO v_r12_wip_lines             FROM wip.mlog$_wip_lines;
   SELECT count(*) INTO v_r12_bom_operation_networ  FROM bom.mlog$_bom_operation_networ;
   SELECT count(*) INTO v_r12_mtl_demand            FROM inv.mlog$_mtl_demand;
   SELECT count(*) INTO v_r12_mtl_onhand_quantitie  FROM inv.mlog$_mtl_onhand_quantitie;
   SELECT count(*) INTO v_r12_po_supplier_item_cap  FROM po.mlog$_po_supplier_item_cap;
   SELECT count(*) INTO v_r12_wip_discrete_jobs     FROM wip.mlog$_wip_discrete_jobs;
   SELECT count(*) INTO v_r12_bom_operational_rout  FROM bom.mlog$_bom_operational_rout;
   SELECT count(*) INTO v_r12_bom_substitute_compo  FROM bom.mlog$_bom_substitute_compo;
   SELECT count(*) INTO v_r12_eam_wo_relationships  FROM eam.mlog$_eam_wo_relationships;
   SELECT count(*) INTO v_r12_mtl_reservations      FROM inv.mlog$_mtl_reservations;
   SELECT count(*) INTO v_r12_mtl_supply            FROM inv.mlog$_mtl_supply;
   SELECT count(*) INTO v_r12_bom_res_instance_cha  FROM bom.mlog$_bom_res_instance_cha;
   SELECT count(*) INTO v_r12_mtl_txn_request_line  FROM inv.mlog$_mtl_txn_request_line;
   SELECT count(*) INTO v_r12_wip_operation_networ  FROM wip.mlog$_wip_operation_networ;
   SELECT count(*) INTO v_r12_wsh_trips             FROM wsh.mlog$_wsh_trips;
   SELECT count(*) INTO v_r12_wsh_trip_stops        FROM wsh.mlog$_wsh_trip_stops;
   SELECT count(*) INTO v_r12_wsm_copy_requirement  FROM wsm.mlog$_wsm_copy_requirement;
   
   INSERT INTO rcs_analysis_r12_temp 
      (script_date
      ,ahl_schedule_materia
         ,bom_operation_sequen
         ,wip_operation_resour
         ,wsm_copy_op_networks
         ,wsm_copy_op_resource
         ,wsm_copy_op_resource1
         ,po_change_requests
         ,wip_operations
         ,wip_op_resource_inst
         ,wip_repetitive_sched
         ,bom_structures_b
         ,mtl_material_transac
         ,mtl_system_items_b
         ,mtl_user_demand
         ,mtl_user_supply
         ,mrp_forecast_items
         ,po_acceptances
         ,wip_requirement_oper
         ,wip_sub_operation_re
         ,bom_operation_resour
         ,bom_resource_changes
         ,bom_sub_operation_re
         ,mtl_item_categories
         ,mrp_forecast_designa
         ,wip_flow_schedules
         ,wip_repetitive_items
         ,wsm_copy_operations
         ,bom_components_b
         ,mrp_forecast_dates
         ,mrp_schedule_dates
         ,oe_order_lines_all
         ,wip_lines
         ,bom_operation_networ
         ,mtl_demand
         ,mtl_onhand_quantitie
         ,po_supplier_item_cap
         ,wip_discrete_jobs
         ,bom_operational_rout
         ,bom_substitute_compo
         ,eam_wo_relationships
         ,mtl_reservations
         ,mtl_supply
         ,bom_res_instance_cha
         ,mtl_txn_request_line
         ,wip_operation_networ
         ,wsh_trips
         ,wsh_trip_stops
         ,wsm_copy_requirement)
   VALUES
      (v_script_date
      ,v_r12_ahl_schedule_materia 
      ,v_r12_bom_operation_sequen 
         ,v_r12_wip_operation_resour 
         ,v_r12_wsm_copy_op_networks 
         ,v_r12_wsm_copy_op_resource 
         ,v_r12_wsm_copy_op_resource1
         ,v_r12_po_change_requests   
         ,v_r12_wip_operations       
         ,v_r12_wip_op_resource_inst 
         ,v_r12_wip_repetitive_sched 
         ,v_r12_bom_structures_b     
         ,v_r12_mtl_material_transac 
         ,v_r12_mtl_system_items_b   
         ,v_r12_mtl_user_demand      
         ,v_r12_mtl_user_supply      
         ,v_r12_mrp_forecast_items   
         ,v_r12_po_acceptances       
         ,v_r12_wip_requirement_oper 
         ,v_r12_wip_sub_operation_re 
         ,v_r12_bom_operation_resour 
         ,v_r12_bom_resource_changes 
         ,v_r12_bom_sub_operation_re 
         ,v_r12_mtl_item_categories  
         ,v_r12_mrp_forecast_designa 
         ,v_r12_wip_flow_schedules   
         ,v_r12_wip_repetitive_items 
         ,v_r12_wsm_copy_operations  
         ,v_r12_bom_components_b     
         ,v_r12_mrp_forecast_dates   
         ,v_r12_mrp_schedule_dates   
         ,v_r12_oe_order_lines_all   
         ,v_r12_wip_lines            
         ,v_r12_bom_operation_networ 
         ,v_r12_mtl_demand           
         ,v_r12_mtl_onhand_quantitie 
         ,v_r12_po_supplier_item_cap 
         ,v_r12_wip_discrete_jobs    
         ,v_r12_bom_operational_rout 
         ,v_r12_bom_substitute_compo 
         ,v_r12_eam_wo_relationships 
         ,v_r12_mtl_reservations     
         ,v_r12_mtl_supply           
         ,v_r12_bom_res_instance_cha 
         ,v_r12_mtl_txn_request_line 
         ,v_r12_wip_operation_networ 
         ,v_r12_wsh_trips            
         ,v_r12_wsh_trip_stops       
         ,v_r12_wsm_copy_requirement);
         
END;
/ 

commit;

Tablespace SQL Commands

SQL #28 -- Get Table Sizes for Tables Exceeding 10 MB

set pages 50
set linesize 200
col bytes format 999,999,999,999 
 
SELECT 
  owner,
  substr(segment_name,1,40) Table_or_Index_Name,
  bytes table_or_index_size,
  tablespace_name,
  partition_name,
  pct_increase,
  blocks,
  extents,
  initial_extent,
  next_extent,
  Min_extents,
  max_extents
FROM 
  dba_segments
WHERE 
  segment_name like 'MSC%'  and
  bytes > 10000000  
  order by tablespace_name, bytes desc;
-- When running data collections and needing to check sizes, the segment_name should also be checked in the transaction instance for 'MRP%' 'WIP%' 'MTL%' 'BOM%' 'OE%' 'MSD%' 'MSD%'

SQL #29 -- Get Tablespace Information for Data Collection Schemas

SELECT 
  fav.application_name app_name,
  fav.application_short_name app_s_name,
  fav.application_id app_id,
  fpi.tablespace,
  fpi.index_tablespace
FROM 
  fnd_application_vl fav,
  fnd_product_installations fpi
WHERE 
  fav.application_id = fpi.application_id  and
  application_short_name in ('WIP','MSC','MSD','MSR','INV','ONT','OE','BOM','MRP','AHL','PO')
  order  by 2;

SQL #30 -- Review Tables and Indices with Zero Percent Increase

This SQL checks that all tables and indices have PCT_INCREASE=0. If that tablename returned ends in MV or SN, then ignore the output.

SELECT 
  owner,
  table_name,
  pct_increase
FROM 
  all_tables
WHERE 
  table_name like 'MSC%'  and
  PCT_INCREASE !=0;
    
SELECT 
  owner,
  table_name,
  index_name,
  pct_increase
FROM 
  all_indexes
WHERE 
  index_name like 'MSC%'  and
  PCT_INCREASE !=0;

SELECT *
   table_owner,
   table_name,
   partition_name,
   pct_increase
FROM 
  all_tab_partitions
  WHERE 
  table_name like 'MSC%'  AND
  PCT_INCREASE !=0;

SQL #31 -- Check the Tablespace Settings

This SQL checks the tablespace settings. PCT_INCREASE should be 0 here also. The tablespace_name might not be the same as below if it was changed during the installation.

select 
  TABLESPACE_NAME,
  INITIAL_EXTENT,
  NEXT_EXTENT,
  MAX_EXTENTS,
 PCT_INCREASE
from
  DBA_TABLESPACES
where
  TABLESPACE_NAME in 
  ('APPS_TS_TX_DATA',
  'APPS_TS_TX_IDX',
  'APPS_TS_SUMMARY',
  'APPS_TS_INTERFACE',
  'APPS_TS_NOLOGGING',
  'APPS_TS_ARCHIVE'); 
-- FOR OLD 11.5.9 and below tablespace mode being used
where
  TABLESPACE_NAME like ('MSC%');

SQL #32 -- Check the Total Space Available in the Tablespaces

This SQL checks the total space available in the tablespaces.

SELECT 
 tablespace_name ,
  round(sum(BYTES)/power(2,20)) TOTAL_MB
FROM 
  dba_data_files
where
  TABLESPACE_NAME in 
  ('APPS_TS_TX_DATA',
  'APPS_TS_TX_IDX',
  'APPS_TS_SUMMARY',
  'APPS_TS_INTERFACE',
  'APPS_TS_NOLOGGING',
  'APPS_TS_ARCHIVE')
group by TABLESPACE_NAME;
-- FOR OLD 11.5.9 and below tablespace mode being used
where
  TABLESPACE_NAME like ('MSC%');

SQL #33 -- Check Total Free Space in MSC Tablespace

SELECT 
  TABLESPACE_NAME,
  round(sum(BYTES)/power(2,20)) FREE_MB
FROM 
  (select   BYTES,
  TABLESPACE_NAME  from DBA_FREE_SPACE
where
  TABLESPACE_NAME in 
  ('APPS_TS_TX_DATA',
  'APPS_TS_TX_IDX',
  'APPS_TS_SUMMARY',
  'APPS_TS_INTERFACE',
  'APPS_TS_NOLOGGING',
  'APPS_TS_ARCHIVE'))
group by TABLESPACE_NAME;
-- FOR OLD 11.5.9 and below tablespace mode being used
where
  TABLESPACE_NAME like ('MSC%');

SQL #34 -- List Individual Datafiles and Space Allocation

This SQL will list the individual datafiles and space allocated to each file.

SELECT 
  substr(tablespace_name,1,8) TS_NAME,
  substr(file_name,1,40) FILE_NAME,
  bytes/1024/1024 MB,
  autoextensible,
  file_id
FROM 
  dba_data_files
where
  TABLESPACE_NAME in 
  ('APPS_TS_TX_DATA',
  'APPS_TS_TX_IDX',
  'APPS_TS_SUMMARY',
  'APPS_TS_INTERFACE',
  'APPS_TS_NOLOGGING',
  'APPS_TS_ARCHIVE')
order by tablespace_name, file_id;
-- FOR OLD 11.5.9 and below tablespace mode being used
where
  TABLESPACE_NAME like ('MSC%');

SQL #35 -- Check Individual Datafiles for Free Space Available

SELECT 
  tablespace_name,
  sum(bytes/1024/1024)  MB,
  file_id
FROM 
  dba_free_space
where
  TABLESPACE_NAME in 
  ('APPS_TS_TX_DATA',
  'APPS_TS_TX_IDX',
  'APPS_TS_SUMMARY',
  'APPS_TS_INTERFACE',
  'APPS_TS_NOLOGGING',
  'APPS_TS_ARCHIVE')
group by tablespace_name, file_id
order by tablespace_name, file_id;
-- FOR OLD 11.5.9 and below tablespace mode being used
where
  TABLESPACE_NAME like ('MSC%');
group by tablespace_name, file_id
order by tablespace_name, file_id;