13 Replies Latest reply on Sep 15, 2010 11:22 AM by 797963

    ORA-01775: looping chain of synonyms

    498339
      Hi!

      We have a very annoying situation. One developer who was logged into database as user A run following SQL statements (don't ask me why):
      CREATE OR REPLACE PUBLIC SYNONYM "USER_TABLES" FOR "USER_TABLES";
      CREATE OR REPLACE PUBLIC SYNONYM "DBMS_STATS" FOR "DBMS_STATS";
      CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR "DUAL";
      As a result lot of thing stopped working in database. For example
      select 1 from dual;
      
      gives ORA-01775: looping chain of synonyms
      
      If I log on as SYS and try to recreate correct synonum:
      
      SQL> create public synonym dual for sys.dual;
      create public synonym dual for sys.dual
      *
      ERROR at line 1:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-01775: looping chain of synonyms
      Does anybody have any ideas how to fix this issue without point in time recovery?
        • 1. Re: ORA-01775: looping chain of synonyms
          618702
          Dear user495336,

          The explanation to that error is as follows;
          ORA-01775: looping chain of synonyms 
          Cause: Through a series of CREATE synonym statements, a synonym was defined that referred to itself. For example, the following definitions are circular:
          
          
          CREATE SYNONYM s1 for s2 CREATE SYNONYM s2 for s3 CREATE SYNONYM s3 for s1 
           
          Action: Change one synonym definition so that it applies to a base table or view and retry the operation.  
          The chain goes back to the first synonym and you have had the error.

          You have to find the blocking and trailing synonym group or groups and speak to your developer.

          Ogan
          • 2. Re: ORA-01775: looping chain of synonyms
            498339
            There is nothing else the developer can tell but those 3 command. If look for problematic synonyms
            select owner, synonym_name, table_name, table_owner, connect_by_iscycle CYCLE
            from dba_synonyms
            where connect_by_iscycle > 0
            connect by nocycle prior table_owner = 'schem_name_goes_here'
            and prior table_name = synonym_name;
            
            PUBLIC     DBMS_STATS     DBMS_STATS     schema_name 1
            PUBLIC     DUAL             DUAL             schema_name 1
            PUBLIC     USER_TABLES     USER_TABLES     schema_name 1
            If we take DUAL it should point to SYS.DUAL not to schema_name.DUAL (there is no object called DUAL in the schema where developer executed SQL). Question is why can't I change the synonym so that it would point to SYS.DUAL and it's also interestin why database allowed to execute a command which will mess things up so badly?
            • 3. Re: ORA-01775: looping chain of synonyms
              Chinar
              select 1 from dual;

              gives ORA-01775: looping chain of synonyms

              If I log on as SYS and try to recreate correct synonum:

              SQL> create public synonym dual for sys.dual;
              create public synonym dual for sys.dual
              *+
              ERROR at line 1:
              ORA-00604: error occurred at recursive SQL level 1
              ORA-01775: looping chain of synonyms

              Dear user495336 ,dropping or replacing data dictionary as result your database could be corrupted.Revoke important system privileges from your developer which he/she actually executed this command.And give this only need privileges.
              Question is why can't I change the synonym so that it would point to SYS.DUAL
              You can change,below example you will see that.
              and it's also interestin why database allowed to execute a command which will mess things up so badly?
              Yes oracle allow that,you can create public synonym,which target object actually does not exists.In below i create synonym W actually such object does not exists.Therefore i get an error.
              C:\Documents and Settings\Administrator>sqlplus smart/s6c457f61@nbc
              
              SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 19 21:57:42 2010
              
              Copyright (c) 1982, 2005, Oracle.  All rights reserved.
              
              
              Connected to:
              Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
              With the Partitioning, OLAP and Data Mining options
              
              SQL> CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR "DUAL";
              
              Synonym created.
              
              SQL> select 1 from dual;
              select 1 from dual
                            *
              ERROR at line 1:
              ORA-01775: looping chain of synonyms
              
              
              SQL> select OWNER,OBJECT_NAME from dba_objects
                2  where OBJECT_NAME='DUAL';
              
              OWNER
              ------------------------------
              OBJECT_NAME
              --------------------------------------------------------------------------------
              
              SYS
              DUAL
              
              PUBLIC
              DUAL
              
              
              SQL> CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR sys.DUAL;
              
              Synonym created.
              
              SQL> select 1 from dual;
              
                       1
              ----------
                       1
              
              SQL> select * from dual;
              
              D
              -
              X
              
              SQL> create public synonym w for sys.w;
              
              Synonym created.
              
              SQL> select * from w;
              select * from w
                            *
              ERROR at line 1:
              ORA-01775: looping chain of synonyms
              
              
              SQL> select * from dual;
              
              D
              -
              X
              
              SQL>
              • 4. Re: ORA-01775: looping chain of synonyms
                498339
                >
                Dear user495336 ,dropping or replacing data dictionary as result your database could be corrupted.Revoke important system privileges from your developer which he/she actually executed this command.And give this only need privileges.
                It's interesting that developer didn't have any special privliges. Just create session, create table, create view and create procedure.

                SQL> CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR sys.DUAL;
                
                Synonym created.
                
                SQL> select 1 from dual;
                
                1
                ----------
                1
                Unforunately I'll get error here:

                ERROR at line 1:
                ORA-00604: error occurred at recursive SQL level 1
                ORA-01775: looping chain of synonyms

                If I log on with developers' account I can't make any synonym even for objects that does exist in that schema. All I get is ORA-01775: looping chain of synonyms. It seems like there is no way out of from that loop :(
                • 5. Re: ORA-01775: looping chain of synonyms
                  sb92075
                  It seems like there is no way out of from that loop :(
                  I have to ask.
                  Can you DROP the SYNONYM?
                  • 6. Re: ORA-01775: looping chain of synonyms
                    498339
                    sb92075 wrote:
                    Can you DROP the SYNONYM?
                    No I can't. When I try to drop it I'll get the same error
                     ORA-01775: looping chain of synonyms 
                    • 7. Re: ORA-01775: looping chain of synonyms
                      618702
                      Dear user495336,

                      You need to start with the first synonym i guess.

                      Ogan
                      • 8. Re: ORA-01775: looping chain of synonyms
                        Chinar
                        Log in developer`s user and execute CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR sys.DUAL; and post result there
                        • 9. Re: ORA-01775: looping chain of synonyms
                          498339
                          CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR sys.DUAL
                           
                          ORA-00604: error occurred at recursive SQL level 1
                          ORA-01775: looping chain of synonyms 
                          • 10. Re: ORA-01775: looping chain of synonyms
                            Chinar
                            Can you execute select from dual* from others users sessions?
                            Also are you sure that your developer executed only below commands?

                            CREATE OR REPLACE PUBLIC SYNONYM "USER_TABLES" FOR "USER_TABLES";
                            CREATE OR REPLACE PUBLIC SYNONYM "DBMS_STATS" FOR "DBMS_STATS";
                            CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR "DUAL";
                            • 11. Re: ORA-01775: looping chain of synonyms
                              Richard Harrison .
                              Hi Have you tried

                              create table a.dual (col1 number); -- use any definition you want

                              then try and drop/replace the synonym?

                              cheers,
                              Harry
                              • 12. Re: ORA-01775: looping chain of synonyms
                                498339
                                Chinar wrote:
                                Can you execute select from dual* from others users sessions?
                                No I can't. It's not possible to use dual at all.
                                Also are you sure that your developer executed only below commands?

                                CREATE OR REPLACE PUBLIC SYNONYM "USER_TABLES" FOR "USER_TABLES";
                                CREATE OR REPLACE PUBLIC SYNONYM "DBMS_STATS" FOR "DBMS_STATS";
                                CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR "DUAL";
                                I'm not 100% sure, but those are the synonyms that I will get from the following query:
                                select owner, synonym_name, table_name, table_owner, connect_by_iscycle CYCLE
                                from dba_synonyms
                                where connect_by_iscycle > 0
                                connect by nocycle prior table_owner = 'schem_name_goes_here'
                                and prior table_name = synonym_name;
                                • 13. Re: ORA-01775: looping chain of synonyms
                                  797963
                                  hello,

                                  I did this stupid mistake by issuing: CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR "DUAL"; onto my Oracle 10g instance.

                                  now I can not event start my instance nor drop this looping synonym (dual)

                                  is there any workaround but drop and recreate my instance ?

                                  tri
                                  jakarta, indonesia