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' ;
成功建立该物化视图
