用events 跟踪解决不能创建物化试图一例

核心提示： 创建MV的代码:CREATE MATERIALIZED VIEW subscription_tab BUILD IMMEDIATE REFRESH COMPLETE START WITH SYSDATE NEXT TRUNC ( SYSDATE ) + 1AS SELECT * FROM

创建MV的代码:

CREATE MATERIALIZED VIEW subscription_tab BUILD IMMEDIATE REFRESH COMPLETE START WITH SYSDATE NEXT TRUNC ( SYSDATE ) + 1AS SELECT * FROM subscription_tab@SMGR;

不料想,执行之后语句报告错误:

SQL > CREATE MATERIALIZED VIEW SUBSCRIPTION_TAB2 BUILD IMMEDIATE3 REFRESH complete START WITH SYSDATE NEXT trunc( SYSDATE ) + 14 AS SELECT * FROM SUBSCRIPTION_TAB@SMGR ;AS SELECT * FROM SUBSCRIPTION_TAB@SMGR*ERROR at line 4 :ORA - 00942 : table or view does not exist .

描述对象:

SQL>desc SUBSCRIPTION_TAB@SMGR;SQL>select count(*) from SUBSCRIPTION_TAB@SMGR;

发现输出正常. 检查远程接口对象(SUBSCRIPTION_TAB)属性:为正常的数据表. 检查后得知该表无主键, 但是和ORA-00942错误无关. 暂且不表.

查找Metalink,搜索出来一堆的帖子,归纳一下可能的原因:

___FCKpd___3

快刀斩乱麻加上胡乱猜测,判定以上皆非.( 此过程花费时间若干,眼睛花了好几回. )

还有我们有最后一招:

set events '942 trace name errorstack level 10' ....

设定跟踪:

SQL>ALTER SESSION SET max_dump_file_size = UNLIMITED;Session altered .SQL>ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';Session altered .SQL>ALTER SESSION SET EVENTS '942 trace name errorstack level 10';Session altered .SQL> CREATE MATERIALIZED VIEW SUBSCRIPTION_TAB2 BUILD IMMEDIATE3 REFRESH complete START WITH SYSDATE NEXT trunc( SYSDATE ) + 14 AS SELECT * FROM SUBSCRIPTION_TAB@SMGR ;AS SELECT * FROM SUBSCRIPTION_TAB@SMGR*ERROR at line 4 :ORA - 00942 : table or view does not exist......