我熟悉ORA-01775错误背后的问题,但是否有调试技巧,或者我只能通过“创建或替换”的方式解决它?
是否有一种查询模式或其他方法,可以查找公共同义词的当前定义是什么?
更棒的是有一个图形化工具,但目前为止,任何东西都会很有帮助。
事实证明,问题并不是一连串的同义词循环,而是同义词指向不存在的视图。
在这种情况下,Oracle 显然会报错为循环链。
sys.plan_table$
。 - William Robertson数据字典表DBA_SYNONYMS
包含数据库中所有同义词的信息。因此,您可以运行查询
SELECT table_owner, table_name, db_link
FROM dba_synonyms
WHERE owner = 'PUBLIC'
AND synonym_name = <<synonym name>>
查看公共同义词当前指向的对象。
这个错误代码的不太直观的解决方案似乎是同义词所指向的对象存在问题。
以下是我用于查找指向错误对象的同义词的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';
尝试使用以下选择来查找问题同义词,它列出了所有指向不存在对象(表、视图、序列、包、过程、函数)的同义词。
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
)
)
AND NOT EXISTS ( SELECT * FROM all_objects WHERE object_type NOT IN ( 'SYNONYM' ) AND dba_synonyms.table_name = all_objects.OBJECT_NAME )
- mxdspselect 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')
您不必深入研究其他类型的无效对象,只需处理那些实际上处于无限循环中的对象。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
。我们通过运行以下修复了这个问题(感谢如何重新创建公共同义词“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;
步骤1)查看与名称相对应的对象:
select * from all_objects where object_name = upper('&object_name');
可能存在同义词,但不存在表格吗?
步骤2)如果那不是问题的原因,就调查同义词:
select * from all_synonyms where synonym_name = upper('&synonym_name');
Package went to invalid state due to this error ORA-01775
.
With the error line number , We went thru the package
body code and found the code which was trying to insert data into a table
.
We ran below queries to check if the above table
and synonym
exists.
SELECT * FROM DBA_TABLES WHERE TABLE_NAME = '&TABLE_NAME'; -- No rows returned
SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME = '&SYNONYM_NAME'; -- 1 row returned
With this we concluded that the table needs to be re- created. As the synonym
was pointing to a table
that did not exist.
DBA team re-created the table and this fixed the issue.