如何在 Oracle 中检查所有无效的同义词
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14983139/
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 check all invalid synonyms in Oracle
提问by aaljovic
I have the table user_synonyms in which I can see the name, table and table owner. Is there a way to see if this synonym is still valid i,e. if the referencing table still exists without trying it manually?
我有一个表 user_synonyms，我可以在其中看到名称、表和表所有者。有没有办法查看这个同义词是否仍然有效，即 如果不手动尝试引用表仍然存在？
回答by Ben
You can check whether the table exists by joining on ALL_TABLES (a synonym might not be on a table in the same schema).
您可以通过加入 ALL_TABLES 来检查表是否存在（同义词可能不在同一架构中的表上）。
select *
from all_synonyms s
left outer join all_tables t
on s.table_owner = t.owner
and s.table_name = t.table_name
where s.owner = user
Add the condition and t.table_name is nullif you want those synonyms where the table does not exist.
and t.table_name is null如果您想要那些表不存在的同义词，请添加条件。
If you want to check whether the synonym is VALID query ALL_OBJECTS.
如果要检查同义词是否为 VALID 查询ALL_OBJECTS。
select *
from all_synonyms s
join all_objects o
on s.owner = o.owner
and s.synonym_name = o.object_name
where o.object_type = 'SYNONYM'
and s.owner = user
and o.status <> 'VALID'
As a_horse_with_no_name points out in the comments there is no requirement for a synonym to be on a table, views, sequences even packages are all valid.
正如 a_horse_with_no_name 在评论中指出的那样，表、视图、序列甚至包都不需要同义词。
So, you might want to change the first query to look for these as well:
因此，您可能希望更改第一个查询以查找这些：
select *
from all_synonyms s
join all_objects o
on s.table_owner = o.owner
and s.table_name = o.object_name
where s.owner = user
回答by Praveen Kumar
Use the below query:
使用以下查询：
select s.table_owner, s.table_name
from all_synonyms s, all_tables t
where s.table_owner = t.owner(+)
and s.table_name = t.table_name(+)
and t.owner is null
--s.owner = 'SCHEMA_NAME'
;
回答by Greg Moffatt
select distinct os.*
from all_objects os
,all_synonyms s
where 1 = 1
and os.object_type = 'SYNONYM'
and os.STATUS = 'INVALID'
and os.object_name = s.synonym_name
and not exists ( select unique(1)
from all_objects o1
where o1.object_name = s.TABLE_NAME
and o1.owner = s.TABLE_OWNER)
order by 2;
