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

    ORA-01775: looping chain of synonyms

    498339
    498339 Aug 19, 2010 3:26 PM
      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?
      • 25251 Views
      • Tags:
        • 1. Re: ORA-01775: looping chain of synonyms
          618702
          618702 Aug 19, 2010 3:32 PM (in response to 498339)
          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
            498339 Aug 19, 2010 3:43 PM (in response to 618702)
            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
              Chinar Aug 19, 2010 5:04 PM (in response to 498339)
              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
                498339 Aug 19, 2010 7:03 PM (in response to Chinar)
                >
                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
                  sb92075 Aug 19, 2010 7:25 PM (in response to 498339)
                  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
                    498339 Aug 23, 2010 9:47 AM (in response to sb92075)
                    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
                      618702 Aug 23, 2010 9:48 AM (in response to 498339)
                      Dear user495336,

                      You need to start with the first synonym i guess.

                      Ogan
                      • 8. Re: ORA-01775: looping chain of synonyms
                        Chinar
                        Chinar Aug 23, 2010 9:50 AM (in response to 498339)
                        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
                          498339 Aug 23, 2010 11:36 AM (in response to Chinar) 
                          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
                            Chinar Aug 23, 2010 11:46 AM (in response to 498339)
                            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 .
                              Richard Harrison . Aug 23, 2010 12:10 PM (in response to 498339)
                              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
                                498339 Aug 23, 2010 12:28 PM (in response to Chinar)
                                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
                                  797963 Sep 15, 2010 11:22 AM (in response to 498339)
                                  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