ORA-12052,不能建立快速重新整理物化檢視的解決

SQL> CREATE MATERIALIZED VIEW mv_vcard_last_hlrlist refresh fast as
select vcard.username,
vcard.nick_name,vcard.pcode,hlrlist.region,
vcard.birth_year, vcard.icon, vcard.descr, last.state,
vcard.HLR_CITY, vcard.BIRTH_MONTH, vcard.BIRTH_DAY
from hlrlist ,last ,vcard
where vcard.username = last.username and vcard.hlr_city = hlrlist.hlr_id
and vcard.status = 0 and last.state='Online' ;
2 3 4 5 6 7 8 and vcard.status = 0 and last.state='Online'
*
ERROR at line 8:
ORA-12052: cannot fast refresh materialized view IMPS.MV_VCARD_LAST_HLRLIST

begin
DBMS_MVIEW.EXPLAIN_MVIEW('select vcard.username,vcard.nick_name,vcard.pcode,hlrlist.region,vcard.birth_year, vcard.icon, vcard.descr, last.state,vcard.HLR_CITY, vcard.BIRTH_MONTH, vcard.BIRTH_DAY from hlrlist ,last ,vcard where vcard.username = last.username and vcard.hlr_city = hlrlist.hlr_id');
END;

SELECT CAPABILITY_NAME, POSSIBLE, MSGTXT
FROM MV_CAPABILITIES_TABLE
WHERE CAPABILITY_NAME LIKE 'REFRESH%';

CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
REFRESH_COMPLETE Y

REFRESH_FAST N

REFRESH_FAST_AFTER_INSERT N
the SELECT list does not have the rowids of all the detail tables

CAPABILITY_NAME P
------------------------------ -
MSGTXT
--------------------------------------------------------------------------------
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 materialized view

發現是因為沒有包括各個表的rowid

於是改為:

CREATE MATERIALIZED VIEW mv_vcard_last_hlrlist refresh fast as
select hlrlist.rowid arowid,last.rowid browid,vcard.rowid crowid, vcard.username,
vcard.nick_name,vcard.pcode,hlrlist.region,
vcard.birth_year, vcard.icon, vcard.descr, last.state,
vcard.HLR_CITY, vcard.BIRTH_MONTH, vcard.BIRTH_DAY
from hlrlist ,last ,vcard
where vcard.username = last.username and vcard.hlr_city = hlrlist.hlr_id and last.state='Online' ;

成功建立該物化檢視

[@more@]