ORA-01775: looping chain of synonyms - What is the SQL to find the synonyms causing the problem?
Hi,
I logged into a newly created APEX environment
and went to the Home>SQL Workshop>SQL Scripts
and got the following error:
Invalid exists/not exists condition: ORA-01775: looping chain of synonyms
I have not created any objects yet.
I looked up:
ORA-01775: looping chain of synonyms
Cause: Through a series of CREATE synonym statements, a synonym was defined that referred to itself. For example, the following definitions are circular:
CREATE SYNONYM s1 for s2 CREATE SYNONYM s2 for s3 CREATE SYNONYM s3 for s1
Action says: Change one synonym definition so that it applies to a
base table or view and retry the operation.
What is the SQL to find the synonyms causing the problem?
I logged into a newly created APEX environment
and went to the Home>SQL Workshop>SQL Scripts
and got the following error:
Invalid exists/not exists condition: ORA-01775: looping chain of synonyms
I have not created any objects yet.
I looked up:
ORA-01775: looping chain of synonyms
Cause: Through a series of CREATE synonym statements, a synonym was defined that referred to itself. For example, the following definitions are circular:
CREATE SYNONYM s1 for s2 CREATE SYNONYM s2 for s3 CREATE SYNONYM s3 for s1
Action says: Change one synonym definition so that it applies to a
base table or view and retry the operation.
What is the SQL to find the synonyms causing the problem?
ASKER
File not found: /site/content.cfm
Orphaned Synonyms: Michael Barwell: Shows orphaned synonyms i.e. syns which point TO objects that no longer exist . ... (0.8 Kb)
it is gone....
Orphaned Synonyms: Michael Barwell: Shows orphaned synonyms i.e. syns which point TO objects that no longer exist . ... (0.8 Kb)
it is gone....
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the query returns 353 rows.
so I just drop them all and the problem goes away?
They are definately not used for anything?
so I just drop them all and the problem goes away?
They are definately not used for anything?
I would exclude those for SYS and SYSTEM owner.
SELECT s.*
FROM ALL_SYNONYMS s
LEFT JOIN ALL_OBJECTS o
ON o.OWNER = s.TABLE_OWNER
AND o.OBJECT_NAME = s.TABLE_NAME
WHERE o.OBJECT_NAME IS NULL
AND o.OWNER NOT IN ( 'SYS', 'SYSTEM' )
ASKER
don't you mean s.OWNER
AND s.OWNER NOT IN ( 'SYS', 'SYSTEM' )
they are all owned by PUBLIC
AND s.OWNER NOT IN ( 'SYS', 'SYSTEM' )
they are all owned by PUBLIC
yes, sorry, s.owner...
if they are owned by public , it should not be a problem.
you might upload the listing in a file, if you want me to check...
if they are owned by public , it should not be a problem.
you might upload the listing in a file, if you want me to check...
the only solution is to drop the synonym in question or recreate an object with that name.
how to find:
http://www.telligence.com.au/site/index.cfm?display=18718
Orphaned Synonyms: Michael Barwell: Shows orphaned synonyms i.e. syns which point TO objects that no longer exist . ... (0.8 Kb)