ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性

Question

CREATE MATERIALIZED VIEW PEOPLE_SHOW
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT
        P.ROOM_ID AS roomId,
        P.PEOPLE_ID AS peopleId,
        P.PEOPLE_NAME AS peopleName,
        P.PEOPLE_GENDER AS peopleGender,
        FLOOR(MONTHS_BETWEEN(SYSDATE,PEOPLE_BIRTHDAY)/12) AS peopleAge,
        P.PEOPLE_BIRTHDAY AS peopleBirthday,
        P.PEOPLE_NATIONALITY AS peopleNationality,
        P.PEOPLE_IDCARD AS peopleIdcard,
        P.PEOPLE_POLITICS_STATUS AS peoplePoliticsStatus,
        P.PEOPLE_EDUCATION AS peopleEducation,
        P.PEOPLE_HOBBY AS peopleHobby,
        P.PEOPLE_REWARD AS peopleReward,
        P.REG_DOMICILE AS regDomicile,
        P.MILI_SERVICE AS miliService,
        P.BASIC_ALLOWANCE AS basicAllowance,
        P.LOW_RENT_HOUSE AS lowRentHouse,
        P.ECONOMY_TRIAL_HOUSE AS economyTrialHouse,
        P.ENJOY AS enjoy,
        P.UNEMP_CERTIFICATE AS unempCertificate,
        P.OLDAGE_INSURANCE AS oldageInsurance,
        P.MEDICAL_INSURANCE AS medicalInsurance,
        P.XINNONGBAO AS xinnongbao,
        P.DISABLED AS disabled,
        P.MARTYR_MEMBER AS martyrMember,
        P.ORPHAN AS orphan,
        P.MARRIED AS married,
        P.PEOPLE_MOBILE AS peopleMobile,
        P.PEOPLE_TEL AS peopleTel,
        P.PEOPLE_WORKPLACE AS peopleWorkplace,
        P.PEOPLE_WORKPLACE_PROPERTY AS peopleWorkplaceProperty,
        P.PEOPLE_WORKPLACE_TEL AS peopleWorkplaceTel,
        P.PUB_RENT_HOUSE AS pubRentHouse,
        P.PUB_SERVICE AS pubService,
        P.DISABLED_SOLDIER AS disabledSoldier,
        P.PROPERTY_OWNER AS propertyOwner,
        P.PROPERTY_OWNER_REL AS propertyOwnerRel,
        P.PEOPLE_ADDRESS AS peopleAddress,
        UI.UNIT_NAME  AS roomCommunity,
        S.STREET_NAME AS roomStreet,
        B.BUILDING_NAME AS roomBuildingNo,
        R.ROOM_HOUSE_NO AS roomHouseNo
      FROM PEOPLE P,ROOM R ,STREET S,BUILDING B,UNIT_INFO UI
      WHERE R.ROOM_ID=P.ROOM_ID AND
      S.STREET_ID=R.ROOM_STREET AND
      B.BUILDING_ID=R.ROOM_BUILDING_NO AND B.DEL_SIGN=1 AND
      UI.UNIT_INFO_ID=R.ROOM_COMMUNITY
ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性sql

Answer 1

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view 
Cause: The materialized view did not satisfy conditions for refresh at commit time.
Action: Specify only valid options. 
问题描述表叫模糊。看一下yangtingkun的blog:http://yangtingkun.itpub.net/post/468/13318Oracle提供的DBMS_MVIEW.EXPLAIN_MVIEW过程可以帮助你快速定位问题的原因，
使用EXPLAIN_MVIEW过程首先要建立MV_CAPABILITIES_TABLE表，建表的脚本是$ORACLE_HOME/rdbms/admin/utlxmv.sql。
（EXPLAIN_MVIEW过程是两个过程的重载，一个输出到MV_CAPABILITIES_TABLE表，另一个以PL/SQL的VARRAY格式输出，为了简单起见，建议建立MV_CAPABILITIES_TABLE表）。