http://dbaspot.com/oracle-errors/286206-ora-32313-refresh-fast-string-string-unsupported-after-pmops.html
[]s Chiappa --- Em oracle_br@yahoogrupos.com.br, "f.miola" escreveu > > Olá pessoal, > > Criei a seguinte MV: > CREATE MATERIALIZED VIEW CRM_LA_MART.MV_F_TARGET_BRAND_IND013 > BUILD IMMEDIATE > REFRESH FAST ON DEMAND > ENABLE QUERY REWRITE > AS > SELECT DM_F_TARGET_ACTIVITY_BRAND_IND.DWH_SOURCE_COUNTRY_CD, > DM_F_TARGET_ACTIVITY_BRAND_IND.DWH_CYCLE_ID, > DM_F_TARGET_ACTIVITY_BRAND_IND.DWH_TERRITORY_ID, > REPLACE(DM_F_TARGET_ACTIVITY_BRAND_IND.TABI_ATVT_TYPE, ' Call', '') AS > TABI_ATVT_TYPE, > DM_D_CYCLE.CYTM_CYCLE_YEAR_STATUS_SEQ, > DM_D_CYCLE.CYTM_CLOSING_STATUS, > DM_D_CYCLE.CYTM_MAT_GROUP, > DM_D_CYCLE.CYTM_YEAR_STATUS, > DM_D_TERRITORY_HIERARCHY.TRHR_COUNTRY_NAME, > DM_D_THERAPEUTIC_AREA.THAR_NAME, > DM_D_BRAND.BRND_PARENT_NAME, > DM_D_INDICATION_LA.INTN_NAME, > DM_D_INDICATION_LA.INTN_INDICATION_SHORT_CODE, > DM_D_ACCOUNT.DWH_ID AS DWH_ACCOUNT, > DM_D_CONTACT.DWH_ID AS DWH_CONTACT, > DM_D_TERRITORY_HIERARCHY.DWH_ID AS DWH_TRH, > MAX(DM_F_TARGET_ACTIVITY_BRAND_IND.TABI_PLANNED_ACTV_QTY_CYCLE) AS > TABI_PLANNED_ACTV_QTY_CYCLE, > MAX(DM_F_TARGET_ACTIVITY_BRAND_IND.TABI_SUGGESTED_ACTV_QTY_CYCLE) AS > TABI_SUGGESTED_ACTV_QTY_CYCLE, > COUNT(*) AS TOTAL > FROM DM_F_TARGET_ACTIVITY_BRAND_IND, > DM_D_ACCOUNT, > DM_D_CONTACT, > DM_D_TERRITORY_HIERARCHY, > DM_D_THERAPEUTIC_AREA, > DM_D_BRAND, > DM_D_INDICATION_LA, > DM_D_CYCLE > WHERE DM_F_TARGET_ACTIVITY_BRAND_IND.DWH_ACCOUNT_ID = DM_D_ACCOUNT.DWH_ID > AND DM_F_TARGET_ACTIVITY_BRAND_IND.DWH_CONTACT_ID = DM_D_CONTACT.DWH_ID > AND DM_F_TARGET_ACTIVITY_BRAND_IND.DWH_TERRITORY_ID = > DM_D_TERRITORY_HIERARCHY.DWH_ID > AND DM_F_TARGET_ACTIVITY_BRAND_IND.DWH_THERAPEUTIC_AREA_ID = > DM_D_THERAPEUTIC_AREA.DWH_ID > AND DM_F_TARGET_ACTIVITY_BRAND_IND.DWH_BRAND_ID = DM_D_BRAND.DWH_ID > AND DM_F_TARGET_ACTIVITY_BRAND_IND.DWH_INDICATION_ID = > DM_D_INDICATION_LA.DWH_ID > AND DM_F_TARGET_ACTIVITY_BRAND_IND.DWH_CYCLE_ID = DM_D_CYCLE.DWH_ID > GROUP BY DM_F_TARGET_ACTIVITY_BRAND_IND.DWH_SOURCE_COUNTRY_CD, > DM_F_TARGET_ACTIVITY_BRAND_IND.DWH_CYCLE_ID, > DM_F_TARGET_ACTIVITY_BRAND_IND.DWH_TERRITORY_ID, > REPLACE(DM_F_TARGET_ACTIVITY_BRAND_IND.TABI_ATVT_TYPE,' Call',''), > DM_D_CYCLE.CYTM_CYCLE_YEAR_STATUS_SEQ, > DM_D_CYCLE.CYTM_CLOSING_STATUS, > DM_D_CYCLE.CYTM_MAT_GROUP, > DM_D_CYCLE.CYTM_YEAR_STATUS, > DM_D_TERRITORY_HIERARCHY.TRHR_COUNTRY_NAME, > DM_D_THERAPEUTIC_AREA.THAR_NAME, > DM_D_BRAND.BRND_PARENT_NAME, > DM_D_INDICATION_LA.INTN_NAME, > DM_D_INDICATION_LA.INTN_INDICATION_SHORT_CODE, > DM_D_ACCOUNT.DWH_ID, > DM_D_CONTACT.DWH_ID, > DM_D_TERRITORY_HIERARCHY.DWH_ID; > > A tabela DM_F_TARGET_ACTIVITY_BRAND_IND é particionada by List (DWH_CYCLE_ID) > e antes da carga são executados split partition e truncate partition. > No momento de fazer Refresh Fast, o seguinte erro é apresentado: > ORA-32313: REFRESH FAST of "CRM_LA_MART"."MV_F_TARGET_BRAND_IND013" > unsupported after PMOPs. > > Alguém pode me ajudar a resolver o problema? > Seguem os logs: > > CREATE materialized view log on DM_D_CYCLE > WITH PRIMARY KEY, SEQUENCE, ROWID (CYTM_YEAR_STATUS, CYTM_CLOSING_STATUS, > CYTM_MAT_GROUP, CYTM_CYCLE_YEAR_SEQ, CYTM_CYCLE_YEAR_STATUS_SEQ, > CYTM_WORKING_DAYS) > INCLUDING NEW VALUES; > > CREATE MATERIALIZED VIEW LOG ON DM_D_ACCOUNT > WITH PRIMARY KEY, SEQUENCE, ROWID > INCLUDING NEW VALUES; > > CREATE MATERIALIZED VIEW LOG ON DM_D_CONTACT > WITH PRIMARY KEY, SEQUENCE, ROWID > INCLUDING NEW VALUES; > > CREATE MATERIALIZED VIEW LOG ON DM_D_TERRITORY_HIERARCHY > WITH PRIMARY KEY, SEQUENCE, ROWID (TRHR_TERRITORY_NAME, > TRHR_TERRITORY_USR_ALIAS, TRHR_COUNTRY_NAME, TRHR_TERRITORY_GROUP_OWNER, > TRHR_LEVEL, > TRHR_BUSINESS_UNIT_NAME, TRHR_NATIONAL_SM_OWNER, TRHR_TERRITORY_OWNER) > INCLUDING NEW VALUES; > > CREATE MATERIALIZED VIEW LOG ON DM_D_THERAPEUTIC_AREA > WITH PRIMARY KEY, SEQUENCE, ROWID (THAR_NAME) > INCLUDING NEW VALUES; > > CREATE MATERIALIZED VIEW LOG ON DM_D_INDICATION_LA > WITH PRIMARY KEY, SEQUENCE, ROWID (INTN_NAME, INTN_INDICATION_SHORT_CODE) > INCLUDING NEW VALUES; > > CREATE MATERIALIZED VIEW LOG ON DM_D_BRAND > WITH PRIMARY KEY, SEQUENCE, ROWID (BRND_NAME, BRND_PARENT_NAME) > INCLUDING NEW VALUES; > > CREATE materialized view log on DM_F_TARGET_ACTIVITY_BRAND_IND > with primary key, rowid, sequence (DWH_SOURCE_COUNTRY_CD, DWH_CYCLE_ID, > DWH_TERRITORY_ID, TABI_ATVT_TYPE, TABI_PLANNED_ACTV_QTY_CYCLE, > TABI_SUGGESTED_ACTV_QTY_CYCLE, DWH_ACCOUNT_ID, DWH_CONTACT_ID, > DWH_THERAPEUTIC_AREA_ID, DWH_BRAND_ID, DWH_INDICATION_ID, TABI_KOL_FLAG, > TABI_EXCEEDED_ACTV_QTY_CYCLE, TABI_EFFECTIVE_ACTV_QTY_CYCLE, > TABI_COMPLETED_ACTV_QTY_CYCLE, TABI_NOT_PLANNED_ACTV_QTY, > TABI_FTE__PRORATED_QTY, > TABI_DEFECT_ACTV_QTY_CYCLE, TABI_FAILED_ACTV_QTY_CYCLE, > TABI_ACCOMP_ACTV_QTY_CYCLE, DWH_MATRIX_SEGM_ID, DWH_BEHAV_SEGM_ID, > DWH_SPECIALTY_ID) > including new values; > > Grata. > Fernanda >