最近在工作中,需要经过DBlink同步数据。由于是一个小型的统计系统。决定创建物化试图(materialized view) 便于两边系统的数据同步。远程系统是另一家公司维护的产品，接口也是由他们提供。按照接口标准创建DBlink后.准备创建物化试图.
创建MV的代码:
CREATE MATERIALIZED VIEW subscription_tab
BUILD IMMEDIATE REFRESH COMPLETE
START WITH SYSDATE
NEXT TRUNC ( SYSDATE ) + 1
AS SELECT * FROM subscription_tab@SMGR;
不料想,执行之后语句报告错误:
SQL > CREATE MATERIALIZED VIEW SUBSCRIPTION_TAB
2 BUILD IMMEDIATE
3 REFRESH complete START WITH SYSDATE NEXT trunc( SYSDATE ) + 1
4 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,搜索出来一堆的帖子,归纳一下可能的原因:
1 远程对象为同义词,而对应的表无mv log
2 global_name 的问题
3 Bug.
4 MLOG$_ 的问题.要重新创建MV log
...
N... others...
快刀斩乱麻加上胡乱猜测,判定以上皆非.( 此过程花费时间若干,眼睛花了好几回. )
还有我们有最后一招:
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_TAB
2 BUILD IMMEDIATE
3 REFRESH complete START WITH SYSDATE NEXT trunc( SYSDATE ) + 1
4 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.COM
Error encountered : ORA - 00942
----------------------------------------------------------------------------
SELECT "witsdba"."SUBSCRIPTION_TAB".CURRVAL@SMGR.US.ORACLE.COM
FROM DUAL@SMGR.US.ORACLE.COM
Error encountered : ORA - 02289
----------------------------------------------------------------------------
SELECT * FROM "PUBLIC"."SUBSCRIPTION_TAB"@SMGR.US.ORACLE.COM
Error encountered : ORA - 00942
----------------------------------------------------------------------------
SELECT "PUBLIC"."SUBSCRIPTION_TAB".CURRVA@SMGR.US.ORACLE.COM
FROM DUAL@SMGR.US.ORACLE.COM
Error encountered : ORA - 02289
----------------------------------------------------------------------------
CREATE MATERIALIZED VIEW SUBSCRIPTION_TAB
BUILD IMMEDIATE
REFRESH complete START WITH SYSDATE NEXT trunc( SYSDATE ) + 1
AS SELECT * FROM SUBSCRIPTION_TAB@SMGR
----------------------------------------------------------------------------
第一句有问题的SQL应该是:
SELECT * FROM "witsdba"."SUBSCRIPTION_TAB"@SMGR.US.ORACLE.COM
先从这句下手,从sqlplus 命令中输入,查询看看:
SQL>SELECT * FROM "witsdba"."SUBSCRIPTION_TAB"@SMGR.US.ORACLE.COM
2 /
SELECT * FROM "witsdba"."SUBSCRIPTION_TAB"@SMGR.US.ORACLE.COM
*
ERROR at line 1 :
ORA - 00942 : table or view does not exist
ORA - 02063 : preceding line from SMGR
...
Faint ,不太可能阿,奇怪,刚才desc SUBSCRIPTION_TAB@SMGR 不是还好好的么? 莫非是,莫非是witsdba 搞的鬼?大小写的问题??
SQL> c/witsdba/WITSDBA
查询,居然OK.看来是遇到了Oracle的一个Bug.
抽取dblink SMGR 的ddl :
CREATE DATABASE LINK SMGR
CONNECT TO "witsdba" IDENTIFIED BY "mypasswd" USING 'smgr';
"witsdba" ??!!删掉,重新创建:
CREATE DATABASE LINK SMGR
CONNECT TO WITSDBA IDENTIFIED BY "mypasswd" USING 'smgr';
执行SQL,创建物化试图.一切正常。
打扫战场,做个总结
以上涉及到具体的表名字因为设计到安全问题均已经作了适当的处理。 整个过程比较关键的还是 alter session set events '942 trace name errorstack level 10' 这一步. 很多时候， 如果DBA在 Metalink上提交一个Tar的话。Oracle技术支持人员会要求用户按照一定操作提交Trace文件。出于其他的原因（开始还以为是个Bug），只好自己动手了。
