如何获取Oracle同义词列列表

Question

我需要在Oracle中获取同义词列列表。创建同义词的表在其他模式中。谁能帮助我解决这个问题？

Answer 1

这将返回DB中所有同义词的列表：

select * from DBA_SYNONYMS
order by synonym_name

这将返回具有"类似"名称的同义词列表：

select * from DBA_SYNONYMS
where upper(synonym_name) like upper('%SYNONYM_NAME_HERE%')
order by synonym_name

响应中相关的列名称：

SYNONYM_NAME  
TABLE_NAME  

Answer 2

这是我用来查看同义词及其目标的查询。您将在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，因为那是一个巨大的列表。

Answer 3

响应永远不会太晚

select * from all_tab_columns@&SYNONYM_DB_LINK
 where upper(table_name) like '&TARGET_TABLE_NAME'
 order by owner, table_name, column_id

Answer 4

select * from all_tab_cols where table_name in 
(select  TABLE_NAME from all_synonyms where owner = <SCHEMA_NAME> )

Answer 5

所有者是同义词所有者

select * from all_synonyms 
 where table_owner=upper(:table_owner) and table_name=upper(:table_name)

"列列表"下的意思是什么？