How to debug ORA-01775: looping chain of synonyms?

Question

I'm familiar with the issue behind ORA-01775: looping chain of synonyms, but is there any trick to debugging it, or do I just have to "create or replace" my way out of it?

Is there a way to query the schema or whatever to find out what the current definition of a public synonym is?

Even more awesome would be a graphical tool, but at this point, anything would be helpful.

Solution 1

As it turns out, the problem wasn't actually a looping chain of synonyms, but the fact that the synonym was pointing to a view that did not exist.

Oracle apparently errors out as a looping chain in this condition.

Solution 2

If you are using TOAD, go to View>Toad Options>Oracle>General and remove TOAD_PLAN_TABLE from EXPLAIN PLAN section and put PLAN_TABLE

Solution 3

The data dictionary table DBA_SYNONYMS has information about all the synonyms in a database. So you can run the query

SELECT table_owner, table_name, db_link
  FROM dba_synonyms 
 WHERE owner        = 'PUBLIC'
   AND synonym_name = <<synonym name>>

to see what the public synonym currently points at.

Solution 4

The less intuitive solution to this error code seems to be problems with the objects that the synonym is pointing to.

Here is my SQL for finding synonyms that point to erroneous objects.

SELECT S.OWNER as SYN_OWNER, S.SYNONYM_NAME as SYN_NAME,
    S.TABLE_OWNER as OBJ_OWNER, S.TABLE_NAME as OBJ_NAME,
    CASE WHEN O.OWNER is null THEN 'MISSING' ELSE O.STATUS END as OBJ_STATUS
FROM DBA_SYNONYMS S
    LEFT JOIN DBA_OBJECTS O ON S.TABLE_OWNER = O.OWNER AND S.TABLE_NAME = O.OBJECT_NAME
WHERE O.OWNER is null
    OR O.STATUS != 'VALID';

Solution 5

Try this select to find the problematic synonyms, it lists all synonyms that are pointing to an object that does not exist (tables,views,sequences,packages, procedures, functions)

SELECT *
FROM dba_synonyms
WHERE table_owner = 'USER'
    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
            )
        )

View more solutions

Comments

[Dave Costa]

Well, something more must have been going on. If I create a view, create a synonym for it, then drop the view, when I try to query the synonym I get: ORA-00980: synonym translation is no longer valid.

[Josh Kodroff]

The tables that the view was pulling from are all tables with public synonyms as well. That may have had something to do with it.

[Dave Jarvis]

AND synonym_name LIKE '%synonym name%' -- a bit more helpful.

[Jeff]

In my case the "missing" table was just owned by a different DB user. All sorts of variations on this theme!

[joezen777]

Thanks. This was my issue within Toad for Oracle.

[David Cramblett]

I received this same error for a public synonym pointing to a table that no longer existed.

[Mladen Oršolić]

I'm trying to insert into newly created table and im getting same error, no clue why :(

[DaaaahWhoosh]

I find it interesting how useful this answer is... for me, I was referring to a nonexistent type when re-adding a procedure to a package. After adding the type back in, the error went away.

[software.wikipedia]

If you do not have access to dba_synonyms you can try same query on all_synonyms

[Kshitiz Sharma]

-1 The OP understands what a looping chain is. What the question asks for is a method to identify which synonyms are broken.

[Kshitiz Sharma]

The best answer. A way to figure out what is broken.

[warren]

@KshitizSharma - apparently you didn't read either the OP or my answer, a quote from Ora-code.com. The "Action" from the quote should have answered his question. And since no one in the last 8 years has felt the need to say it was wrong, your down vote was not especially helpful. Do you have a better answer? If not, and given the near-decade-old question and answer herein, I'd appreciate a reversal of your down vote.

[Kshitiz Sharma]

The OP has asked for a way to query the schema for finding invalid synonyms or graphical tool to locate and fix. The advice to fix it yourself by modifying synonym definition doesn't count as a debugging trick. The age of it, or my lack of a solution have nothing to do with the quality of this one.

[Tim]

without creating a table I created the public synonym,since this table needs to be accessed from another schema, i executed the grant which was not working. i tried the select * from so_and_so_table; the query returned >"ORA-01775 looping chain of synonyms". After creating the table this issue was fixed.

[Andrew Brennan]

This only works for a specific schema and for specific kinds of object. It would work better if it referenced dba_objects.

[Andrew Brennan]

A missing object can create a chain by virtue of being missing

[Daniel Belém Duarte]

@Jarrod Chesney what to do with the results from this query ?

[William Robertson]

Or even better, use sys.plan_table$.

[hslakhan]

@Daniel Belém Duarte recreate the missing object, then recompile. Worked for me. Great piece of code.

[mxdsp]

I would recommand chaning the last condition for : AND NOT EXISTS ( SELECT * FROM all_objects WHERE object_type NOT IN ( 'SYNONYM' ) AND dba_synonyms.table_name = all_objects.OBJECT_NAME )

[Neto Yo]

Oracle and their ever helpful error messages, this was exactly my problem I misstyped the tablename while creating my synonym, I wonder why Oracle allowed me to create the synonym to a non existant object, should have compile it and throw a table or view exception and not create the synonym but wherever.