• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4505
  • Last Modified:

Not able to get an explain plan for any sql statements

When I try to get an explain plan of any sql statements (even like select sysdate from dual;) I am getting error:

Explain Plan Error: ORA-01775: looping chain of synonyms

In the given database I cannot find any synonyms. What can cause this issue and how to fix it?

I am using Toad for Oracle and Oracle 9i.

Thanks.
0
GoodName
Asked:
GoodName
  • 3
  • 3
  • 2
5 Solutions
 
dragos_craciunCommented:
Look for PUBLIC synonyms for PLAN_TABLE.
public synonyms are synonyms that are visible for all schemas, even if they reside on a specific schema

connect using a DBA account and execute:

SELECT * FROM ALL_SYNONYMS s WHERE  s.SYNONYM_NAME = 'PLAN_TABLE'
0
 
dragos_craciunCommented:
For example on one of our systems PLAN_TABLE is a synonym in the schema PUBLIC, pointing to SYS.PLAN_TABLE$
0
 
sdstuberCommented:
It's possible it's the plan_table  but the looping error could be from a looping chain in the parsing and construction of a plan on the statement itself, possibly in the other recursive sql against the dictionary.

How are you getting the plan and when are you getting the error?

Is it in generating the plan?  or in querying the plan?



are you sure this on 9i?  On 10g  you should get a looping error for a public synonym with no base table that points to itself (which is correct).   Prior to 10 you'd get ORA-00980 (which is misleading, hence the change in 10g)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
GoodNameAuthor Commented:
Sorry, you are right it is 10g.

I am using Toad for Oracle to generate Explain Plan and I am using local 10g client/DB to connect to 9i and 10g databases. I am working with several databases and only with one database this issue occurred.

Actually I did not have this issue till few days yesterday.

0
 
sdstuberCommented:
somebody probably created an public synonym plan_table to a non-existent table called plan_table.

Since it's 10g,  you should be using the SYS.PLAN_TABLE$  plan table.

Create (or have your dba create) a public synonym to that table and (unless somebody has a REALLY good reason to keep them around) drop all the rest.
0
 
GoodNameAuthor Commented:
I checked the sys objects and could not find any synonyms or tables like plan_table except sys.plan_table$.

I have created a public synonym for that table but it did not help yet.
0
 
sdstuberCommented:
what is your public synonym?  PLAN_TABLE?

and, if you're using Toad,  what is the plan table Toad is set to?  Is it using TOAD_PLAN_TABLE?  or it using the table pointed to by your public synonym?
0
 
GoodNameAuthor Commented:
Yes, the problem was in Toad settings - TOAD_PLAN_TABLE name. Someone just deleted this table from the database. When I specified a default synonym name - PLAM_TABLE$, everything started to work.

Thanks.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now