Re: MTL_ITEM_CAT_SN and DIRLOAD_DML

Posted by Linda on
URL: http://erman-arslan-s-oracle-forum.114.s1.nabble.com/MTL-ITEM-CAT-SN-and-DIRLOAD-DML-tp1885p1892.html

Hi Erman!
Thanks for checking your instance .Yes the hard issues make our DBA life interesting :)

Yep I run that check.. and  its not helping- me at least :(
SQL> exec dbms_mview.explain_mview('APPS.MTL_ITEM_CATS_SN');
PL/SQL procedure successfully completed.
 SELECT capability_name,
           possible,
              substr(msgtxt,1,60) AS msgtxt
         FROM mv_capabilities_table
       WHERE capability_name like '%FAST%'
REFRESH_FAST                   N
REFRESH_FAST_AFTER_INSERT      N does not meet the requirements of a primary key mv
REFRESH_FAST_AFTER_ONETAB_DML  N see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML     N see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT               N PCT is not possible on any of the detail tables in the mater


Its the setup of the related objects of that MTL_ITEM_CATEGORIES. But what exactly... and the table ofcourse dont have the primary key

Do you have  this table looking like this :
CREATE TABLE MLOG$_MTL_ITEM_CATEGORIES
(
  M_ROW$$          VARCHAR2(255 BYTE),
  SNAPTIME$$       DATE,
  DMLTYPE$$        VARCHAR2(1 BYTE),
  OLD_NEW$$        VARCHAR2(1 BYTE),
  CHANGE_VECTOR$$  RAW(255),
  XID$$            NUMBER
)

CREATE MATERIALIZED VIEW apps.MTL_ITEM_CATS_SN (INVENTORY_ITEM_ID,ORGANIZATION_ID,CATEGORY_SET_ID,CATEGORY_ID,RN)
TABLESPACE APPS_TS_TX_DATA
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             128K
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
            FLASH_CACHE      DEFAULT
            CELL_FLASH_CACHE DEFAULT
           )
NOCACHE
NOLOGGING
NOCOMPRESS
PARALLEL ( DEGREE 4 INSTANCES 1 )
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
/* Formatted on 1/10/2017 6:03:20 PM (QP5 v5.256.13226.35538) */
SELECT "A1"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
       "A1"."ORGANIZATION_ID" "ORGANIZATION_ID",
       "A1"."CATEGORY_SET_ID" "CATEGORY_SET_ID",
       "A1"."CATEGORY_ID" "CATEGORY_ID",
       0 "RN"
  FROM "INV"."MTL_ITEM_CATEGORIES" "A1";

Thanks,Linda