Oracle 通過dblink訪問表,因synonym失效，引起ORA-04045/ORA-16000/ORA-02063

maohaiqing0304發表於2014-11-05
Oracle

標題：Oracle 通過dblink訪問表,因synonym失效，引起ORA-04045/ORA-16000/ORA-02063

作者：ｌōττéｒｙ©版權所有[文章允許轉載,但必須以連結方式註明源地址,否則追究法律責任.]


前景介紹：
環境：RAC（FWSB使用者[讀寫],FWSB_B使用者[只提供plsql連線查詢]） 、DG環境【只讀模式】 ..
需要：多個測試環境與生產環境指令碼對比資料..為了不與RAC環境爭搶資源，將在各測試環境建立連線DG環境的dblink(db_fwsb_b)
某天同事說查詢表報錯【ORA-04045/ORA-16000/ORA-02063】
最終知識點
1、synonym 對映的表重建會導致synonym失效；
2、10g開始 失效物件在再次呼叫時自動會置為有效[包括：synonym/儲存等]；
  

一、 模擬報錯
SQL> SELECT * FROM LOTTERY@db_fwsb ;  --測試環境通過dblink查詢生產環境資料

ORA-04045: 在重新編譯/重新驗證 FWSB_B.LOTTERY 時出錯
ORA-16000: 開啟資料庫以進行只讀訪問
ORA-02063: 緊接著 2 lines (起自 DB_FWSB)
 
SQL> 查詢用到的dblink,許可權
SQL> SELECT * FROM DBA_DB_LINKS WHERE DB_LINK IN ('DB_BACKUP','DB_FWSB');
 
OWNER                          DB_LINK                 USERNAME                       HOST         
------------------------------ -------------------- ------------------- ---------------------------- 
PUBLIC                         DB_FWSB                 FWSB_B                         DG 環境  --只讀
SYS                            DB_BACKUP               FWSB_B                         RAC環境  --可編輯fwsb_b環境synonym
 
SQL> 

二、 報錯分析：
RAC/DG環境查詢物件有效狀態：
SQL> SELECT o.owner,o.object_name,o.object_type,o.status,o.created,o.last_ddl_time,o.timestamp FROM dba_objects o where o.object_name = 'LOTTERY';
 
OWNER                OBJECT_NAME     OBJECT_TYPE         STATUS  CREATED     LAST_DDL_TIME TIMESTAMP
-------------------- --------------------------------------------------------------------
FWSB_B                 LOTTERY       SYNONYM             INVALID 2014/6/8 17 2014/6/8 17:5 2014-06-08:17:55:21
FWSB                   LOTTERY       TABLE               VALID   2014/11/2 3 2014/11/2 4:0 2014-11-02:03:05:18
 
synonym對應的表：
SQL> SELECT * FROM  DBA_SYNONYMS S WHERE S.SYNONYM_NAME IN ('LOTTERY');
 
OWNER             SYNONYM_NAME         TABLE_OWNER                    TABLE_NAME                     DB_LINK
-----------------  -----------------  ------------------------------ ------------------------------ ---------- 
FWSB_B             LOTTERY             FWSB                                         LOTTERY       
 
SQL>

三、分析總結
   不難看出，fwsb_b.LOTTERY的synonym是失效狀態導致測試環境通過dblink查詢生產環境資料包錯ORA-04045/ORA-16000/ORA-02063.
解決方案：
① 重新建立同義詞 【create or replace synonym fwsb_b.lottery for fwsb.lottery； 】
② 重新編譯同義詞 【ALTER  SYNONYM fwsb_b.LOTTERY COMPILE；】、在有許可權的使用者下呼叫一次...

問題：
1、synonym為什麼會變invalid狀態？
2、fwsb.lottery表存在，在DG環境 查詢fwsb_b.lottery同義詞報錯，而在rac環境重新查詢fwsb_b.lottery同義詞不報錯呢？
   難道是和[儲存等的表被重建了，呼叫會重新編輯] 一樣？synonym呼叫也會先試著重新編輯？ 

帶著疑問測試下： 重新建立表 對應表的synonym是否會失效，且重新查詢synonym是否會自動將失效的synonym置為有效：
RAC環境：
SQL>  SELECT O.OWNER,O.OBJECT_NAME,O.OBJECT_TYPE,O.STATUS,O.CREATED,O.LAST_DDL_TIME,O.TIMESTAMP FROM DBA_OBJECTS O WHERE O.OBJECT_NAME = 'LOTTERY';
 
OWNER                          OBJECT_NAME                       OBJECT_TYPE         STATUS  CREATED     LAST_DDL_TIME TIMESTAMP
------------------------------ ------------------------------ ------------------- ------- ----------- ------------- -------------------
FWSB_B                         LOTTERY                             SYNONYM             VALID   2014/11/5 1 2014/11/5 11: 2014-11-05:11:18:58
FWSB                           LOTTERY                             TABLE               VALID   2014/11/5 1 2014/11/5 11: 2014-11-05:11:18:58
 
SQL> drop table fwsb.lottery purge;
 
Table dropped
 
SQL> create table fwsb.lottery as select * from dual;
 
Table created
 
SQL>
SQL>  SELECT O.OWNER,O.OBJECT_NAME,O.OBJECT_TYPE,O.STATUS,O.CREATED,O.LAST_DDL_TIME,O.TIMESTAMP FROM DBA_OBJECTS O WHERE O.OBJECT_NAME = 'LOTTERY';
 
OWNER                          OBJECT_NAME                       OBJECT_TYPE         STATUS    CREATED     LAST_DDL_TIME TIMESTAMP
------------------------------ ------------------------------ ------------------- ------- ----------- ------------- -------------------
FWSB_B                         LOTTERY                             SYNONYM             INVALID   2014/11/5 1 2014/11/5 11: 2014-11-05:11:18:58
FWSB                           LOTTERY                             TABLE               VALID     2014/11/5 1 2014/11/5 11: 2014-11-05:11:18:58
 
SQL>

測試環境：
SQL> SELECT * FROM LOTTERY@db_fwsb ;    --使用連線DG的dblink查詢測試下
 
SELECT * FROM LOTTERY@db_fwsb
 
ORA-04045: 在重新編譯/重新驗證 FWSB_B.LOTTERY 時出錯
ORA-16000: 開啟資料庫以進行只讀訪問
ORA-02063: 緊接著 2 lines (起自 DB_FWSB)       
 

SQL> SELECT count(1) FROM LOTTERY@db_backup;  --因為【db_backup有對lottery同義詞重新編譯許可權】 所以select 呼叫就相當於重新編譯synonym 了...
 
  COUNT(1)
----------
        99
 
SQL> 
SQL>  SELECT O.OWNER,O.OBJECT_NAME,O.OBJECT_TYPE,O.STATUS,O.CREATED,O.LAST_DDL_TIME,O.TIMESTAMP FROM DBA_OBJECTS O WHERE O.OBJECT_NAME = 'LOTTERY';
 
OWNER                          OBJECT_NAME         OBJECT_TYPE         STATUS  CREATED     LAST_DDL_TIME TIMESTAMP
------------------------------ --------------- --------------- ------------------- ------- ----------- ------------- -------------------
FWSB_B                         LOTTERY              SYNONYM             VALID   2014/11/5 1 2014/11/5 11: 2014-11-05:11:28:38
FWSB                           LOTTERY              TABLE               VALID   2014/11/5 1 2014/11/5 11: 2014-11-05:11:27:40
 
SQL> 
SQL> SELECT * FROM LOTTERY@db_fwsb;       --再使用連線DG的dblink查詢可以了
 
DUMMY
-----
X
 
SQL> 


擴充套件查詢：
SQL> SELECT s.owner_name, s.name, s.type_num, s.type_name, s.status, s.ctime FROM KU$_SCHEMAOBJ_VIEW s【或者SYS.KU$_SYNONYM_VIEW】 where name IN ('LOTTERY');
 
OWNER_NAME                     NAME          TYPE_NUM TYPE_NAME                        STATUS CTIME
------------------------------ ------------------------------ ---------- ---------------------------- ---------- -------------------
FWSB                           LOTTERY              2 TABLE                                 1 2014-11-02 04:23:19
FWSB_B                         LOTTERY              5 SYNONYM                   (有效狀態是1) 5 2014-06-08 17:55:21
 
--KU$_SCHEMAOBJ_VIEW【或者SYS.KU$_SYNONYM_VIEW】 沒找到 相關資料

總結：
SYNONYM和PROCEDURE..等物件一樣，物件中的表重新建立後，失效的SYNONYM/PROCEDURE..再呼叫時會自動重新編輯
DG環境查下synonym 報錯是因為DG只讀環境 沒有權限執行ALTER  SYNONYM fwsb_b.LOTTERY COMPILE；且ORA-04045帶顏色部分已經編輯很清楚了..重新編輯出錯
所以通過實驗結合報錯提示 明瞭了吧！

 


此條目發表在 Oracle 分類目錄。將固定連線加入收藏夾。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28602568/viewspace-1321645/，如需轉載，請註明出處，否則將追究法律責任。

相關文章