用10046進行診斷一例

今天，一直執行正常的物化檢視重新整理忽然執行不正常
SQL> exec dbms_refresh.refresh('WAI_REFRESH');

begin dbms_refresh.refresh('WAI_REFRESH'); end;

ORA-02019: connection description for remote database not found
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

但是手工重新整理正常（item屬於WAI_REFRESH重新整理組中）
SQL> exec dbms_snapshot.refresh('item');

PL/SQL procedure successfully completed

檢查DB LINK情況
SQL> select distinct master_link from user_mviews;

MASTER_LINK
--------------------------------------------------------------------------------
@WAI.SOUCHANG.COM

SQL> select * from ;

DUMMY
-----
X

一切正常。百思不得其解，決定用10046事件試試到底發生什麼。
SQL> @gettrace

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/admin/sc2test/udump/sc2test_ora_30715.trc

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered

SQL> exec dbms_refresh.refresh('WAI_REFRESH');

begin dbms_refresh.refresh('WAI_REFRESH'); end;

ORA-02019: connection description for remote database not found
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

用tkprof格式化/opt/oracle/admin/sc2test/udump/sc2test_ora_30715.trc，有如下可疑資訊
The following statement encountered a error during parse:

select errcount from where destination = 'IMAGE.SOUCHANG.COM'

Error encountered: ORA-02019

檢查user_refresh_children，發現有一個OWNER是test使用者的，它在test_tag_group使用者下是一個同義詞

SQL> select OWNER,NAME,ROWNER from user_refresh_children;

OWNER NAME ROWNER
------------------------------ ------------------------------ ------------------------------
TEST_TAG_GROUP USERS TEST_TAG_GROUP
TEST IMAGE TEST_TAG_GROUP
TEST_TAG_GROUP AUTHORITIES TEST_TAG_GROUP
TEST_TAG_GROUP AVERREVIEWRATE TEST_TAG_GROUP
TEST_TAG_GROUP CATEGORY TEST_TAG_GROUP
TEST_TAG_GROUP CHANNEL TEST_TAG_GROUP
TEST_TAG_GROUP CLOB_CONTENT TEST_TAG_GROUP
TEST_TAG_GROUP FLEA_MARKET_INFO TEST_TAG_GROUP
TEST_TAG_GROUP ITEM TEST_TAG_GROUP
TEST_TAG_GROUP ITEM_TAG TEST_TAG_GROUP
TEST_TAG_GROUP KEYWORD TEST_TAG_GROUP
TEST_TAG_GROUP LOGS TEST_TAG_GROUP

.....................

進一步檢查發現，test_tag_group使用者下並沒有@IMAGE.SOUCHANG.COM這個DBLINK。
知道錯誤原因了，問題就容易解決了。在本例中，只需在test_tag_group中建立新建一個與IMAGE.SOUCHANG.COM一樣的DB LINK 即可。也可以把test使用者下的DB LINK修改為public型別的。

最後關閉10046事件
SQL> alter session set events '10046 trace name context off';

Session altered