ORA-01775: looping chain of synonyms

error while creating synonms for new users

ORA-01775: looping chain of synonyms

this error raised because of a post user creation trigger 

-----------------------------------------------------------------------------------------------------

Declare
  Cursor vc_syn Is
 Select 'Create Synonym '|| U.Db_User_Id||'.'||O.Object_Name || ' For ' || :Global.Owner || '.' || object_name  Str
      From dba_Objects O, Sec_Users U, dba_Users A
     Where O.Owner      = :Global.Owner
       and U.User_Id      = :Blk_1.User_Id
       and U.Db_User_Id   = A.Username
       and O.Object_Type In ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'SYNONYM')
       and Not Exists (Select 'X'
                         From Dba_Synonyms
                        Where Table_Owner  = :Global.Owner
                          and Owner       = U.Db_User_Id
                     and Synonym_Name = O.Object_Name);
  Dummy    Number(2);
  vn_count Number := 0;
        
Begin  
   Forms_DDL ('GRANT ' || :Global.Owner ||'_' || :Blk_2.Group_Id || ' TO '|| :Global.Owner||'_' || :Blk_1.User_Id);
   If Form_Failure Then
    Message(sqlerrm);
    pause;
    Raise Form_Trigger_Failure;
   End if;

   Begin
       Select 1
          Into Dummy
          From Dba_Synonyms
         Where Owner = :Blk_1.User_Id ;
   Exception
         When No_Data_Found Then
         For Rec In vc_syn Loop
             Forms_DDL(Rec.Str);
         End Loop;
   End;
End;

---------------------------------------------------------------------------------------------------

this select statement in trigger was throwing this error

Select 'Create Synonym '|| U.Db_User_Id||'.'||O.Object_Name || ' For ' || :Global.Owner || '.' || object_name  Str
      From dba_Objects O, Sec_Users U, dba_Users A
     Where O.Owner       = :Global.Owner
       and U.User_Id      = :Blk_1.User_Id
       and U.Db_User_Id   = A.Username
       and O.Object_Type In ('TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'SYNONYM')
       and Not Exists (Select 'X'
                         From Dba_Synonyms
                        Where Table_Owner  = :Global.Owner
                          and Owner        = U.Db_User_Id
                      and Synonym_Name = O.Object_Name);

----------------------------------------------------------------------------------------------

when i tried same select statement with user sys it was running fine but while running from other users it was giving errors.

-----------------------------------------------------------------------------------------------

The reason was that there was a public synonym in db named dba_synonyms which was pointing towards a private synonym under a user HOME schema with same name dba_synonyms and this private synonym was pointing towards a sys table dba_synonyms 

upon cleaning of some unnecessary objects synonym in HOME schema was deleted and now this public synonym was unable to find private synonym under HOME schema so it was throwing ORA-01775. 

so now situation was 

public.dba_synonyms    >>>    HOME.dba_synonyms (HOME Private synonym) >>>> was pointing to SYS.DBA_SYNONYMS 

so now synonym in red was missing. 

when i was running same query with sys user it was running fine because it was selecting from table dba_synonyms which was in sys own schema. but other users were calling public synonym which was unable to call HOME.dba_synonyms 

so i created it for user HOME

create synonym DBA_SYNONYMS  for SYS.DBA_SYNONYMS 

and everything starting working fine.