存储过程使用dblink问题
按照接口标准创建DBlink后. 准备创建物化视图:

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

发现输出正常.

检查远程接口对象(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
...................
此过程如果执行中如果提示用户无权限,需要作适当的授权
然后找到我们的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-rw---- 1 oracle oracle 1677 May 14 14:49 stat_ora_1046.trc
-rw-rw---- 1 oracle oracle 631 May 15 19:35 stat_ora_14864.trc
-rw-rw---- 1 oracle oracle 631 May 15 19:42 stat_ora_15187.trc
-rw-rw---- 1 oracle oracle 631 May 15 20:57 stat_ora_18540.trc
-rw------- 1 oracle oracle 943813 May 15 21:41 stat_ora_20358.trc //This one!

打开该文件,看了半天,没看太明白.乱糟糟的一大堆内容,头疼.还是格式化一下再看:

[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".CURRVAL@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
--------------------------
[oracle@stat udump]$ tkprof stat_ora_20358.trc SNAPSHOT.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: