Solved

Not able to get an explain plan for any sql statements

Posted on 2008-06-17
8
3,938 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
  • 3
  • 3
  • 2
8 Comments
 
LVL 6

Assisted Solution

by:dragos_craciun
dragos_craciun earned 100 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 100 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 73

Assisted Solution

by:sdstuber
sdstuber earned 400 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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 400 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 73

Accepted Solution

by:
sdstuber earned 400 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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 shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now