關於oracle synonym 的總結整理

今天同事問我一個問題，說他發現有一個同義詞的問題很奇怪，不知怎麼解釋，具體描述如下：

在使用者codprov使用者下，建立了兩個同義詞，一個是public的，一個是本使用者下的，但是同義詞的名稱一樣，codprov使用者有查詢cod使用者下所有物件的許可權。

create or replace public synonym TB_OFR_CUST_REP_DETAIL for COD.VW_OFR_CUST_REP_DETAIL;
create or replace synonym codprov.TB_OFR_CUST_REP_DETAIL for COD.TB_OFR_CUST_REP_DETAIL;

COD.VW_OFR_CUST_REP_DETAIL的內容如下：
CREATE OR REPLACE VIEW COD.VW_OFR_CUST_REP_DETAIL AS
SELECT rd.DISC_RULE_ID DISC_RULE_ID,
       rd.STAT_SECT_ID STAT_SECT_ID,
       rd.MONEY_ITEM MONEY_ITEM,
       rd.MONEY_CLAC_TYPE MONEY_CLAC_TYPE,
       rd.DISC_COUNT DISC_COUNT
  FROM MV_OFR_CUST_REP_PRI rp,
       MV_OFR_CUST_REP_DETAIL rd
 WHERE rp.DISC_RULE_ID = rd.DISC_RULE_ID
   AND rp.MONEY_TYPE = '2'
UNION
SELECT rd.DISC_RULE_ID DISC_RULE_ID,
       'A' || rd.DISC_RULE_ID || '_9999' STAT_SECT_ID,
       rd.MONEY_ITEM MONEY_ITEM,
       rd.MONEY_CLAC_TYPE MONEY_CLAC_TYPE,
       rd.DISC_COUNT DISC_COUNT
  FROM MV_OFR_CUST_REP_PRI rp,
       MV_OFR_CUST_REP_DETAIL rd
 WHERE rp.DISC_RULE_ID = rd.DISC_RULE_ID
   AND rp.MONEY_TYPE = '1';
問題：
查詢:select * from tb_ofr_cust_rep_detail;能得出如下結果：
DISC_RULE_ID STAT_SECT_ID MONEY_ITEM MONEY_CLAC_TYPE DISC_COUNT
1 A1_9999 1 1 .02
1 A1_9999 2 2 10
1 A1_9999 3 2 0
1 A1_9999 4 2 15
10016 A10016_9999 1 1 .02

查詢：SELECT * FROM CODPROV.TB_OFR_CUST_REP_DETAIL;也得出如下結果：
DISC_RULE_ID STAT_SECT_ID MONEY_ITEM MONEY_CLAC_TYPE DISC_COUNT
1 A1_9999 1 1 .02
1 A1_9999 2 2 10
1 A1_9999 3 2 0
1 A1_9999 4 2 15
10016 A10016_9999 1 1 .02

可是查詢：select * from cod.tb_ofr_cust_rep_detail;卻報錯ORA-00942:表和檢視不存在，而查詢同義詞SELECT * FROM CODPROV.TB_OFR_CUST_REP_DETAIL時卻沒有報ORA-00980同義詞轉換不再有效的錯誤。是為什麼？

經過分析，發現tb_ofr_cust_rep_detail在cod下確實不存在，因此查詢：select * from cod.tb_ofr_cust_rep_detail報ORA-00942:表和檢視不存在是必然的，而SELECT * FROM CODPROV.TB_OFR_CUST_REP_DETAIL時卻沒有報ORA-00980同義詞轉換不再有效的錯誤，那是因為這個使用者下建立了一個public的同義詞，在codprov使用者下建立私有的同義詞：create or replace synonym codprov.TB_OFR_CUST_REP_DETAIL for COD.TB_OFR_CUST_REP_DETAIL時;對映的COD.TB_OFR_CUST_REP_DETAIL實際上是先前建立的public同義詞，而他們的名稱正好一致，所有給人以錯覺，以為他引用的還是cod.tb_ofr_cust_rep_detail的表。如果不建立這個public同義詞，那麼查詢SELECT * FROM CODPROV.TB_OFR_CUST_REP_DETAIL必定會報ORA-00980同義詞轉換不再有效的錯誤。或者說建立的PUBLIC同義詞名稱如果和建立私有同義詞說對映的物件的名稱不一樣時，那麼查詢私有同義詞時也會報ORA-00980錯誤。    --這個問題得到解釋。

這裡順便說一下：

1.如果在某一個使用者下，建立了一個PUBLIC同義詞，如果查詢不帶使用者名稱查詢是不會報表或檢視不存在的錯誤，如果帶上使用者名稱查詢會則報ORA-00942表或檢視錯誤，因為這個使用者下確實沒有這個物件。

2.如果再在這個使用者下建立一個同名的物件，表、檢視或者同義詞，那麼不管帶不帶使用者名稱查詢，他都會查詢這個使用者私有的物件，不會查詢那個public的同義詞，這一點特別要注意，如果在生產環境中，可能會造成很大的資料錯誤。

最後的總結是，一般情況下儘量避免建立public的同義詞，這樣會造成很多隱晦的問題，除非不得以才要建。哪個使用者需要，就在哪個使用者下建立本使用者下的同義詞，這樣比較清晰。

 

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