ORA-01775 looping chain of synonyms

Problem: Query feback ORA-01775: looping chain of synonyms

SQL>
SELECT IM.ID,
       IM.NAME,
       IM.SID,
       IM.SNAME,
       IM.INUMBER,
       to_date(IM.IDATE, 'yyyy/mm/dd') IDATE_STR,
       IM.TT,
       IM.TA,
       IM.NOTE,
       ' ' AS DESCRIPTION
  FROM PFS_INV_META_S_2016 IM
  LEFT JOIN PFS_CRT_INV_SAVE_MT_2016 MT1     
    ON MT1.TRANSACTION_ID = IM.STRANSACTION_ID   
  LEFT JOIN PFS_CRT_INV_MT_2016 MT2         
    ON MT2.TRANSACTION_ID = IM.STRANSACTION_ID   
 WHERE 1 = 1                                       
   AND IM.PARID1 = SUBSTR('00009999', -1)     
   AND IM.ESTATUS = '2'   
   AND IM.TT = '2'    
   AND IM.ID = '00009999'
   AND IM.SID = '54321321'
   AND IM.BT IN ('1', '2');

  FROM PFS_INV_META_S_2016 IM
       *
ERROR at line 11:
ORA-01775: looping chain of synonyms

Check synonyms map for owner table

SQL> select owner, synonym_name, table_owner, table_name from dba_synonyms where synonym_name = 'PFS_INV_META_S_2016';

OWNER      SYNONYM_NAME              TABLE_OWNE TABLE_NAME
---------- ------------------------- ---------- -------------------------
PUBLIC     PFS_INV_META_S_2016       PEEE       PFS_INV_META_S_2016

Check the synonym map for the table, check if the table exists

SQL> select count(*) from PFS_INV_META_S_2016;
select count(*) from PFS_INV_META_S_2016
                     *
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL> select count(*) from PEEE.PFS_INV_META_S_2016; 
select count(*) from PEEE.PFS_INV_META_S_2016
                          *
ERROR at line 1:
ORA-00942: table or view does not exist

Soultion: Rebuild the table and synonym.

張貼留言

0 留言