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

文章作者 100test 发表时间 2007:06:26 12:39:48
来源 100Test.Com百考试题网

由于是一个小型的统计系统。决定创建物化试图(materialized view) 便于两边系统的数据同步。远程系统是另一家公司维护的产品，接口也是由他们提供。按照接口标准创建DBlink后.准备创建物化试图.

创建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 [email protected]>0select count(*) from SUBSCRIPTION_TAB@SMGR.
发现输出正常. 检查远程接口对象(SUBSCRIPTION_TAB)属性:为正常的数据表. 检查后得知该表无主键, 但是和ORA-00942错误无关. 暂且不表.

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

1 远程对象为同义词,而对应的表无mv log2 global_name 的问题3 Bug.4 MLOG出处：http://www.dbanotes.net
版本：0.9

--------------------------------------------------------------------------------

最近在工作中,需要经过DBlink同步数据。由于是一个小型的统计系统。决定创建物化试图(materialized view) 便于两边系统的数据同步。远程系统是另一家公司维护的产品，接口也是由他们提供。按照接口标准创建DBlink后.准备创建物化试图.

创建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 [email protected]>0select 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......
此过程如果执行中如果提示用户无权限,需要作适当的授权. 退出SQL*Plus,在具体的目录中找到我们的 Trace 文件:

[oracle@stat udump]$ ls - ltr......- rw ------- 1 oracle oracle 1425501 May 13 18 : 23 stat_ora_1512 . trc- rw ------- 1 oracle oracle 1518962 May 13 18 : 26 stat_ora_1595 . trc- rw ------- 1 oracle oracle 1519241 May 13 18 : 27 stat_ora_1689 . trc- rw ------- 1 oracle oracle 1486910 May 13 18 : 31 stat_ora_1700 . trc- rw ------- 1 oracle oracle 943813 May 15 21 : 41 stat_ora_20358 . trc[oracle@stat udump]$ tkprof stat_ora_20358.trc SNAPSHOT.sql
查看 SNAPSHOT.sql,这回内容还算清晰,发现主要相关内容如下:

The following statements encountered a error during parse :SELECT * FROM "witsdba"."SUBSCRIPTION_TAB"@SMGR.US.ORACLE.COMError encountered : ORA - 00942----------------------------------------------------------------------------SELECT "witsdba"."SUBSCRIPTION_TAB"[email protected] FROM [email protected] encountered : ORA - 02289----------------------------------------------------------------------------SELECT * FROM "PUBLIC"."SUBSCRIPTION_TAB"@SMGR.US.ORACLE.COMError encountered : ORA - 00942----------------------------------------------------------------------------SELECT "PUBLIC"."SUBSCRIPTION_TAB"[email protected] FROM [email protected] encountered : ORA - 02289----------------------------------------------------------------------------CREATE MATERIALIZED VIEW SUBSCRIPTION_TABBUILD IMMEDIATEREFRESH complete START WITH SYSDATE NEXT trunc( SYSDATE ) 1AS SELECT * FROM SUBSCRIPTION_TAB@SMGR----------------------------------------------------------------------------
src="/oracle/js/wxgg_oracle.js">