Solved

Not able to get an explain plan for any sql statements

Posted on 2008-06-17
8
3,992 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
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.

 

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 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

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.

Question has a verified solution.

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

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

823 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