SQL 如何调试 ORA-01775：同义词循环链？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/247090/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to debug ORA-01775: looping chain of synonyms?
提问by Josh Kodroff
I'm familiar with the issue behind ORA-01775: looping chain of synonyms, but is there any trick to debugging it, or do I just have to "create or replace" my way out of it?
我熟悉 ORA-01775 背后的问题：同义词循环链，但是有没有调试它的技巧，或者我是否只需要“创建或替换”我的方式来解决它？
Is there a way to query the schema or whatever to find out what the current definition of a public synonym is?
有没有办法查询模式或其他什么来找出公共同义词的当前定义是什么？
Even more awesome would be a graphical tool, but at this point, anything would be helpful.
更棒的是图形工具，但在这一点上，任何东西都会有所帮助。
回答by Josh Kodroff
As it turns out, the problem wasn't actually a looping chain of synonyms, but the fact that the synonym was pointing to a view that did not exist.
事实证明，问题实际上不是同义词的循环链，而是同义词指向不存在的视图这一事实。
Oracle apparently errors out as a looping chain in this condition.
在这种情况下，Oracle 显然会出错为循环链。
回答by LJT
If you are using TOAD, go to View>Toad Options>Oracle>General and remove TOAD_PLAN_TABLE from EXPLAIN PLAN section and put PLAN_TABLE
如果您使用的是 TOAD，请转至 View>Toad Options>Oracle>General 并从 EXPLAIN PLAN 部分删除 TOAD_PLAN_TABLE 并将 PLAN_TABLE
回答by Justin Cave
The data dictionary table DBA_SYNONYMShas information about all the synonyms in a database. So you can run the query
数据字典表DBA_SYNONYMS包含有关数据库中所有同义词的信息。所以你可以运行查询
SELECT table_owner, table_name, db_link
FROM dba_synonyms
WHERE owner = 'PUBLIC'
AND synonym_name = <<synonym name>>
to see what the public synonym currently points at.
查看公共同义词当前指向的内容。
回答by Jarrod Chesney
The less intuitive solution to this error code seems to be problems with the objects that the synonym is pointing to.
此错误代码的不太直观的解决方案似乎是同义词指向的对象存在问题。
Here is my SQL for finding synonyms that point to erroneous objects.
这是我用于查找指向错误对象的同义词的 SQL。
SELECT S.OWNER as SYN_OWNER, S.SYNONYM_NAME as SYN_NAME,
S.TABLE_OWNER as OBJ_OWNER, S.TABLE_NAME as OBJ_NAME,
CASE WHEN O.OWNER is null THEN 'MISSING' ELSE O.STATUS END as OBJ_STATUS
FROM DBA_SYNONYMS S
LEFT JOIN DBA_OBJECTS O ON S.TABLE_OWNER = O.OWNER AND S.TABLE_NAME = O.OBJECT_NAME
WHERE O.OWNER is null
OR O.STATUS != 'VALID';
回答by Michal Mikolajczyk
Try this select to find the problematic synonyms, it lists all synonyms that are pointing to an object that does not exist (tables,views,sequences,packages, procedures, functions)
尝试此选择以查找有问题的同义词，它列出了指向不存在的对象（表、视图、序列、包、过程、函数）的所有同义词
SELECT *
FROM dba_synonyms
WHERE table_owner = 'USER'
AND (
NOT EXISTS (
SELECT *
FROM dba_tables
WHERE dba_synonyms.table_name = dba_tables.TABLE_NAME
)
AND NOT EXISTS (
SELECT *
FROM dba_views
WHERE dba_synonyms.table_name = dba_views.VIEW_NAME
)
AND NOT EXISTS (
SELECT *
FROM dba_sequences
WHERE dba_synonyms.table_name = dba_sequences.sequence_NAME
)
AND NOT EXISTS (
SELECT *
FROM dba_dependencies
WHERE type IN (
'PACKAGE'
,'PROCEDURE'
,'FUNCTION'
)
AND dba_synonyms.table_name = dba_dependencies.NAME
)
)
回答by Alan
Today I got this error, and after debugging I figured out that the actual tables were misssing, which I was referring using synonyms. So I suggest - first check that whether the tables exists!! :-))
今天我遇到了这个错误，在调试之后我发现缺少实际的表，我使用同义词来指代它。所以我建议 - 首先检查表是否存在！！:-))
回答by Tim Lewis
A developer accidentally wrote code that generated and ran the following SQL statement CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR "DUAL";which caused select * from dba_synonyms where table_name = 'DUAL';
to return PUBLIC DUAL SOME_USER DUALrather than PUBLIC DUAL SYS DUAL.
开发人员不小心编写了生成并运行以下 SQL 语句的代码，该语句CREATE OR REPLACE PUBLIC SYNONYM "DUAL" FOR "DUAL";导致select * from dba_synonyms where table_name = 'DUAL';
返回PUBLIC DUAL SOME_USER DUAL而不是PUBLIC DUAL SYS DUAL.
We were able to fix it (thanks to How to recreate public synonym "DUAL"?) by running
我们能够通过运行来修复它（感谢如何重新创建公共同义词“DUAL”？）
ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=FALSE SCOPE=MEMORY;
CREATE OR REPLACE PUBLIC SYNONYM DUAL FOR SYS.DUAL;
ALTER SYSTEM SET "_SYSTEM_TRIG_ENABLED"=true SCOPE=MEMORY;
回答by mahi_0707
We encountered this error today. This is how we debugged and fixed it.
我们今天遇到了这个错误。这就是我们调试和修复它的方式。
Package went to invalid state due to this error
ORA-01775.With the error line number , We went thru the
packagebody code and found the code which was trying to insert data into atable.We ran below queries to check if the above
tableandsynonymexists.SELECT * FROM DBA_TABLES WHERE TABLE_NAME = '&TABLE_NAME'; -- No rows returned SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME = '&SYNONYM_NAME'; -- 1 row returnedWith this we concluded that the table needs to be re- created. As the
synonymwas pointing to atablethat did not exist.DBA team re-created the table and this fixed the issue.
由于此错误，包进入无效状态
ORA-01775。有了错误行号，我们通过
package正文代码找到了试图将数据插入table.我们跑下面的查询检查上面的
table和synonym存在。SELECT * FROM DBA_TABLES WHERE TABLE_NAME = '&TABLE_NAME'; -- No rows returned SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME = '&SYNONYM_NAME'; -- 1 row returned由此我们得出结论，需要重新创建该表。由于
synonym指向一个table不存在的。DBA 团队重新创建了表，这解决了问题。
回答by Justin
While Jarrod's answer is a good idea, and catches a broader range of related problems, I found this query found in Oracle forums to more directly address the (originally stated) issue:
虽然 Jarrod 的回答是一个好主意，并且解决了更广泛的相关问题，但我发现在 Oracle 论坛中找到的这个查询更直接地解决了（最初陈述的）问题：
select owner, synonym_name, connect_by_iscycle CYCLE
from dba_synonyms
where connect_by_iscycle > 0
connect by nocycle prior table_name = synonym_name
and prior table_owner = owner
union
select 'PUBLIC', synonym_name, 1
from dba_synonyms
where owner = 'PUBLIC'
and table_name = synonym_name
and (table_name, table_owner) not in (select object_name, owner from dba_objects
where object_type != 'SYNONYM')
https://community.oracle.com/message/4176300#4176300
https://community.oracle.com/message/4176300#4176300
You will not have to wade through other kinds of invalid objects. Just those that are actually in endless loops.
您将不必涉足其他类型的无效对象。只是那些实际上处于无限循环中的。
回答by grokster
Step 1) See what Objects exist with the name:
步骤 1) 查看名称中存在哪些对象：
select * from all_objects where object_name = upper('&object_name');
It could be that a Synonym exists but no Table?
可能存在同义词但没有表？
Step 2) If that's not the problem, investigate the Synonym:
步骤 2) 如果这不是问题，请调查同义词：
select * from all_synonyms where synonym_name = upper('&synonym_name');
It could be that an underlying Table or View to that Synonym is missing?
可能是缺少该同义词的基础表或视图？
