Solved

Not able to get an explain plan for any sql statements

Posted on 2008-06-17
8
4,026 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 74

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 74

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 74

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

821 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