如何获取Oracle中同义词的列列表
如何获取Oracle中同义词的列列表
提问于 2016-06-23 16:33:24
我需要在ORACLE中获取同义词的列列表。在其上创建同义词的表位于其他架构中。有人能帮我解决这个问题吗？
回答 5
Stack Overflow用户
发布于 2019-06-12 02:32:42
这将返回数据库中所有同义词的列表：
select * from DBA_SYNONYMS
order by synonym_name这将返回带有“like”名称的同义词列表：
select * from DBA_SYNONYMS
where upper(synonym_name) like upper('%SYNONYM_NAME_HERE%')
order by synonym_name响应中的相关列名：
SYNONYM_NAME
TABLE_NAME Stack Overflow用户
发布于 2019-06-12 03:27:49
下面是一个查询，我用它来查看同义词及其目标。您需要在DBA_SYNONYMS和DBA_OBJECTS上拥有SELECT权限。
select decode(owner, 'PUBLIC', 'PUBLIC SYNONYM', 'SYNONYM') as objtype,
decode(owner, 'PUBLIC', '', owner) as objowner,
synonym_name as objname,
synonym_name || ' => ' ||
case
when db_link is null then '(' || (
select o1.object_type from dba_objects o1 where o1.owner = table_owner and o1.object_name = table_name and o1.object_type in ('VIEW','TABLE','SYNONYM','SEQUENCE','FUNCTION','PROCEDURE','PACKAGE','MATERIALIZED VIEW','JAVA CLASS','TYPE')
and not exists (select 1 from dba_objects o2 where o2.owner = o1.owner and o2.object_name = o1.object_name and o1.object_type = 'TABLE' and o2.object_type = 'MATERIALIZED VIEW')
) || ') ' || table_owner || '.' || table_name
else decode(table_owner, null, '', table_owner || '.') || table_name || decode(db_link, null, '', '@' || db_link)
end as objdesc
from dba_synonyms
where OWNER != 'PUBLIC'
order by 1,2,3,4虽然有点冗长，但输出效果很好，如下所示：
OBJTYPE OBJOWNER OBJNAME OBJDESC
------- ----------- -------------------- -----------------------------------------------------------------------------
SYNONYM SYSTEM PRODUCT_USER_PROFILE PRODUCT_USER_PROFILE => (TABLE) SYSTEM.SQLPLUS_PRODUCT_PROFILE
SYNONYM SYSTEM TAB TAB => (VIEW) SYS.TAB这实际上是我用来查找各种类型的对象的更大查询的一部分，因此是一些冗余信息。
我过滤掉了PUBLIC，因为这是一个很大的列表。
Stack Overflow用户
发布于 2021-04-09 18:41:54
回应永远不会太晚
select * from all_tab_columns@&SYNONYM_DB_LINK
where upper(table_name) like '&TARGET_TABLE_NAME'
order by owner, table_name, column_id页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/37986219
复制相关文章
相似问题