ORA-01775: looping chain of synonyms while executing select command on all_objects?

Question

I am executing a select query on all_objects:

select * from all_objects;

I am getting below error:

select * from all_objects *

ERROR at line 1: ORA-01775: looping chain of synonyms

I am not so sure why this error is coming. What I understand from this error is that a synonyms is defined which somehow reffered to itself. But that error should not come while selecting anything from all_objects. Is my database is corrupted.

P.S. I know enough is asked on this error but all the answers given are not answering my question. I am really new to oracle a solution for this issue will be of great help.

If you limit it to a particular schema do you get the error? `WHERE OWNER='SchemaName'` Could also mean a synonym target is missing: http://stackoverflow.com/questions/23251876/why-oracle-is-complaining-about-a-looping-chain-of-synonyms-ora01775-in-packag — xQbert, Dec 19 '14 at 15:37
Maybe: `select * from user_synonyms where (table_owner, table_name) not in ( SELECT owner, object_name from all_objects );` — xQbert, Dec 19 '14 at 15:45
You could get this error if the view or table name to which the synonym is reffering to has changed or if the view or table has been dropped. — Shankar, Dec 19 '14 at 15:54
Have you created your own private synonym for the `all_tables` view perhaps? xQbert's query ought to show that if so. — Alex Poole, Dec 19 '14 at 18:09
@xQbert Yes! I get the same error for 'WHERE OWNER='SCHEMANAME'' — amod, Dec 20 '14 at 06:24
Also select command mentioned by you also gives me the same error. — amod, Dec 20 '14 at 06:25
Odd... So regardless of schema name you limit by you get the same error. This implies a system Public synonym has been altered in some way. We've moved beyond my basic trouble shooting and probably need insight from someone more adept at DBA activities for oracle. Unless someone here is a DBA, I'd recommend moving this post to a http://dba.stackexchange.com/ as I believe the error to be at more depth than a code change. — xQbert, Dec 22 '14 at 13:33

score 0 · Answer 1 · edited May 23 '17 at 11:57

Try this select to find the problematic synonym..

referred from this link. I couldnt check if it works.

SELECT *
FROM dba_synonyms
WHERE table_owner = 'SYSADM'
    AND (
        NOT EXISTS (
            SELECT *
            FROM dba_tables
            WHERE dba_synonyms.table_name = dba_tables.TABLE_NAME
            )
        AND NOT EXISTS (
            SELECT *
            FROM dba_views
            WHERE dba_synonyms.table_name = dba_views.VIEW_NAME
            )
        AND NOT EXISTS (
            SELECT *
            FROM dba_sequences
            WHERE dba_synonyms.table_name = dba_sequences.sequence_NAME
            )
        AND NOT EXISTS (
            SELECT *
            FROM dba_dependencies
            WHERE type IN (
                    'PACKAGE'
                    ,'PROCEDURE'
                    ,'FUNCTION'
                    )
                AND dba_synonyms.table_name = dba_dependencies.NAME
            )
        )

No it is also giving the same problem. – amod Dec 20 '14 at 06:21 — amod, Dec 20 '14 at 06:21

ORA-01775: looping chain of synonyms while executing select command on all_objects?

1 Answers1