ORA-12052,不能建立快速刷新物化视图的解决-白红宇

ORA-12052,不能建立快速刷新物化视图的解决

阅读量：2426 次

发布时间：2019-05-10

本文共 2240 字，大约阅读时间需要 7 分钟。

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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7916042/viewspace-1002789/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7916042/viewspace-1002789/

你可能感兴趣的文章