DBA工作备忘录之一:用events 跟踪解决不能创建物化试图一例

Fenng · 发表于 2004-5-15 23:00

最近在工作中,需要从DBlink同步数据.

由于是一个小型的统计系统.决定创建物化试图(materialized view)便于两边系统的数据同步.

远程系统是另一家公司维护的产品.接口也是由他们提供.

按照接口标准创建DBlink后.
准备创建物化试图:

[php]
CREATE MATERIALIZED VIEW SUBSCRIPTION_TAB
BUILD IMMEDIATE
REFRESH complete START WITH SYSDATE NEXT trunc(SYSDATE) + 1
AS SELECT * FROM SUBSCRIPTION_TAB@SMGR;

[/php]

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

[php]
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    .

[/php]

Fenng · 发表于 2004-5-15 23:05

SQL>desc SUBSCRIPTION_TAB@SMGR

发现输出正常.

检查远程接口对象(SUBSCRIPTION_TAB)属性:为正常的数据表.

发现该表无主键,但是和ORA-00942错误无关.

暂且不表.

Fenng · 发表于 2004-5-15 23:19

查找Metalink .这种问题找这个最快了

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

1 远程对象为同义词,而对应的表无mv log
2 global_name 的问题
3 Bug.
4 MLOG$_ 的问题.要重新创建MV log
...
n others

快刀斩乱麻加上猜测,发现以上皆非

此过程花费时间若干,眼睛花了好几回.

还有我们有最后一招:

set events '942 trace name errorstack level 10'

Fenng · 发表于 2004-5-15 23:25

设定跟踪,

[php]
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

...................[/php]

此过程如果执行中如果提示用户无权限,需要作适当的授权

然后找到我们的Trace 文件:

[php]

[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!

....
[/php]

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

[oracle@stat udump]$ tkprof stat_ora_20358.trc SNAPSHOT.sql

查看 SNAPSHOT.sql
这回内容还算清晰,发现主要相关内容如下:

[php]

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

[/php]
[oracle@stat udump]$ tkprof stat_ora_20358.trc SNAPSHOT.sql

————————————————————
帮你编辑一下，去掉两个自动加上的email标记
-Kamus

jlandzpa · 发表于 2004-5-15 23:29

不错

Fenng · 发表于 2004-5-15 23:30

[php]SELECT * FROM "witsdba"."SUBSCRIPTION_TAB"@SMGR.US.ORACLE.COM

...
[/php]
先从这句下手,从sqlplus 命令中输入,查询看看:
[php]
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
...
[/php]
Faint ,不太可能阿.

刚才desc SUBSCRIPTION_TAB@SMGR 不是还好好的么?

莫非是,莫非是witsdba导的鬼?

SQL> c/witsdba/WITSDBA

查询,居然OK.

抽取dblink SMGR 的ddl :
[php]
CREATE DATABASE LINK SMGR  CONNECT TO "witsdba"  IDENTIFIED BY "mypasswd"  USING 'smgr';
...
[/php]

"witsdba" ??!!

删掉,重新创建.
[php]
CREATE DATABASE LINK SMGR  CONNECT TO WITSDBA IDENTIFIED BY "mypasswd" USING 'smgr'
/
...
[/php]
执行SQL,创建物化试图.一切正常。

d.c.b.a · 发表于 2004-5-15 23:34

原来如此，真是开了眼。

Fenng · 发表于 2004-5-15 23:39

以上涉及到具体的表名字因为设计到安全问题均已经作了适当的处理。

整个过程比较关键的还是alter session set events '942 trace name errorstack level 10' 这一步

很多时候，如果DBA在Metalink上提交一个Tar的话。Oracle技术支持人员会要求用户按照一定操作提交Trace文件。

出于其他的原因（开始还以为是个Bug），只好自己动手了。而且，瞎猫碰上了死耗子，碰巧解决了问题。

希望对大家有所帮助。欢迎加入讨论

jlandzpa · 发表于 2004-5-15 23:40

前段时间，遇到一个很奇怪的问题：
某个VIEW里用到了db_link, select count(*) 没有问题，
但我recompile 该view 时，却发现该view是失效的．

d.c.b.a · 发表于 2004-5-15 23:41

对event不很了解，'942 trace name errorstack level 10' 每个单词什么意思啊？

[精华] DBA工作备忘录之一:用events 跟踪解决不能创建物化试图一例

打扫战场，做个总结