?
Solved

Not able to get an explain plan for any sql statements

Posted on 2008-06-17
8
Medium Priority
?
4,272 Views
Last Modified: 2013-12-19
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
Comment
Question by:GoodName
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 6

Assisted Solution

by:dragos_craciun
dragos_craciun earned 400 total points
ID: 21804777
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
 
LVL 6

Assisted Solution

by:dragos_craciun
dragos_craciun earned 400 total points
ID: 21804797
For example on one of our systems PLAN_TABLE is a synonym in the schema PUBLIC, pointing to SYS.PLAN_TABLE$
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1600 total points
ID: 21805857
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:GoodName
ID: 21806251
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1600 total points
ID: 21806450
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
 

Author Comment

by:GoodName
ID: 21809571
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 1600 total points
ID: 21810054
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
 

Author Comment

by:GoodName
ID: 21810268
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question