我需要在ORACLE中获取同义词的列列表。在其上创建同义词的表位于其他架构中。有人能帮我解决这个问题吗?
发布于 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
发布于 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
,因为这是一个很大的列表。
发布于 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
https://stackoverflow.com/questions/37986219
复制相似问题