用events跟蹤解決不能創(chuàng)建物化試圖一例
2024-07-21 02:33:49
供稿：網(wǎng)友
最近在工作中,需要經(jīng)過DBlink同步數(shù)據(jù)。由于是一個(gè)小型的統(tǒng)計(jì)系統(tǒng)。決定創(chuàng)建物化試圖(materialized view) 便于兩邊系統(tǒng)的數(shù)據(jù)同步。遠(yuǎn)程系統(tǒng)是另一家公司維護(hù)的產(chǎn)品，接口也是由他們提供。按照接口標(biāo)準(zhǔn)創(chuàng)建DBlink后.預(yù)備創(chuàng)建物化試圖.
創(chuàng)建MV的代碼:
CREATE MATERIALIZED VIEW subscription_tab
BUILD IMMEDIATE REFRESH COMPLETE
START WITH SYSDATE
NEXT TRUNC ( SYSDATE ) + 1
AS SELECT * FROM subscription_tab@SMGR;
不料想,執(zhí)行之后語句報(bào)告錯(cuò)誤:
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;
發(fā)現(xiàn)輸出正常. 檢查遠(yuǎn)程接口對象(SUBSCRIPTION_TAB)屬性:為正常的數(shù)據(jù)表. 檢查后得知該表無主鍵, 但是和ORA-00942錯(cuò)誤無關(guān). 暫且不表.
查找Metalink,搜索出來一堆的帖子,歸納一下可能的原因:
1 遠(yuǎn)程對象為同義詞,而對應(yīng)的表無mv log
2 global_name 的問題
3 Bug.
4 MLOG$_ 的問題.要重新創(chuàng)建MV log
...
N... others...
快刀斬亂麻加上胡亂猜測,判定以上皆非.( 此過程花費(fèi)時(shí)間若干,眼睛花了好幾回. )
還有我們有最后一招:
set events '942 trace name errorstack level 10' ....
設(shè)定跟蹤:
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
......
此過程假如執(zhí)行中假如提示用戶無權(quán)限,需要作適當(dāng)?shù)氖跈?quán). 退出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,這回內(nèi)容還算清楚,發(fā)現(xiàn)主要相關(guān)內(nèi)容如下:
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應(yīng)該是:
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的一個(gè)Bug.
抽取dblink SMGR 的ddl :
CREATE DATABASE LINK SMGR
CONNECT TO "witsdba" IDENTIFIED BY "mypasswd" USING 'smgr';
"witsdba" ??!!刪掉,重新創(chuàng)建:
CREATE DATABASE LINK SMGR
CONNECT TO WITSDBA IDENTIFIED BY "mypasswd" USING 'smgr';
執(zhí)行SQL,創(chuàng)建物化試圖.一切正常。
打掃戰(zhàn)場,做個(gè)總結(jié)
以上涉及到具體的表名字因?yàn)樵O(shè)計(jì)到安全問題均已經(jīng)作了適當(dāng)?shù)奶幚怼?整個(gè)過程比較要害的還是 alter session set events '942 trace name errorstack level 10' 這一步. 很多時(shí)候， 假如DBA在 Metalink上提交一個(gè)Tar的話。Oracle技術(shù)支持人員會(huì)要求用戶按照一定操作提交Trace文件。出于其他的原因（開始還以為是個(gè)Bug），只好自己動(dòng)手了。
