blocks|key|4408599|text|In+OWNER是同义词owner|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4408600|select+*+from+all_synonyms+
+where+table_owner=upper(:table_owner)+and+table_name=upper(:table_name)|code-block|syntax|javascript|4408601|你在“列列表”下面是什么意思？|4408602|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

In OWNER is the synonym owner

<pre><code>select * from all_synonyms 
 where table_owner=upper(:table_owner) and table_name=upper(:table_name)
</code></pre>

What do you mean under "Column list"?

blocks|key|4408495|text|这将返回数据库中所有同义词的列表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4408496|select+*+from+DBA_SYNONYMS
order+by+synonym_name|code-block|syntax|javascript|4408497|这将返回带有“like”名称的同义词列表：|4408498|select+*+from+DBA_SYNONYMS
where+upper(synonym_name)+like+upper('%25SYNONYM_NAME_HERE%25')
order+by+synonym_name|4408499|响应中的相关列名：|4408500|SYNONYM_NAME++
TABLE_NAME++|4408501|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

This will return a list of all synonyms in the db:

<pre><code>select * from DBA_SYNONYMS
order by synonym_name
</code></pre>

This will return a list of synonyms with a 'like' name: 

<pre><code>select * from DBA_SYNONYMS
where upper(synonym_name) like upper('%SYNONYM_NAME_HERE%')
order by synonym_name
</code></pre>

Relevant column names in response: 

<pre><code>SYNONYM_NAME 
TABLE_NAME 
</code></pre>

blocks|key|4408577|text|下面是一个查询，我用它来查看同义词及其目标。您需要在DBA_SYNONYMS和DBA_OBJECTS上拥有SELECT权限。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4408578|select+decode(owner,+'PUBLIC',+'PUBLIC+SYNONYM',+'SYNONYM')+as+objtype,
+++++++decode(owner,+'PUBLIC',+'',+owner)+as+objowner,
+++++++synonym_name+as+objname,
+++++++synonym_name+%7C%7C+'+=>+'+%7C%7C
+++++++case
+++++++++when+db_link+is+null+then+'('+%7C%7C+(
+++++++++++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')
+++++++++)+%7C%7C+')+'+%7C%7C+table_owner+%7C%7C+'.'+%7C%7C+table_name
+++++++++else+decode(table_owner,+null,+'',+table_owner+%7C%7C+'.')+%7C%7C+table_name+%7C%7C+decode(db_link,+null,+'',+'@'+%7C%7C+db_link)
+++++++end+as+objdesc
++from+dba_synonyms
+where+OWNER+!=+'PUBLIC'
+order+by+1,2,3,4|code-block|syntax|javascript|4408579|虽然有点冗长，但输出效果很好，如下所示：|4408580|OBJTYPE+OBJOWNER++++OBJNAME++++++++++++++OBJDESC
-------+-----------+--------------------+-----------------------------------------------------------------------------
SYNONYM+SYSTEM++++++PRODUCT_USER_PROFILE+PRODUCT_USER_PROFILE+=>+(TABLE)+SYSTEM.SQLPLUS_PRODUCT_PROFILE
SYNONYM+SYSTEM++++++TAB++++++++++++++++++TAB+=>+(VIEW)+SYS.TAB|4408581|这实际上是我用来查找各种类型的对象的更大查询的一部分，因此是一些冗余信息。|4408582|我过滤掉了PUBLIC，因为这是一个很大的列表。|4408583|entityMap^0|Q|C|13|B|1H|6|0|0|0|0|0|5|6|0^^$0|@$1|2|3|4|5|6|7|U|8|@$9|V|A|W|B|C]|$9|X|A|Y|B|C]|$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|13|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|14|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|15|8|@$9|16|A|17|B|C]]|D|@]|E|$]]|$1|S|3|-4|5|6|7|18|8|@]|D|@]|E|$]]]|T|$]]

Here is a query that I use to see synonyms and their targets.
You will need <code>SELECT</code> privileges on <code>DBA_SYNONYMS</code> and <code>DBA_OBJECTS</code>.

<pre><code>select decode(owner, 'PUBLIC', 'PUBLIC SYNONYM', 'SYNONYM') as objtype,
 decode(owner, 'PUBLIC', '', owner) as objowner,
 synonym_name as objname,
 synonym_name || ' =&gt; ' ||
 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
</code></pre>

It's a bit wordy, but it makes nice output like this:

<pre><code>OBJTYPE OBJOWNER OBJNAME OBJDESC
------- ----------- -------------------- -----------------------------------------------------------------------------
SYNONYM SYSTEM PRODUCT_USER_PROFILE PRODUCT_USER_PROFILE =&gt; (TABLE) SYSTEM.SQLPLUS_PRODUCT_PROFILE
SYNONYM SYSTEM TAB TAB =&gt; (VIEW) SYS.TAB
</code></pre>

This is actually part of a larger query I use for finding objects of various types, hence some of the redundant information.

I filtered out <code>PUBLIC</code> because that is one huge list.

blocks|key|4408784|text|回应永远不会太晚|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4408785|select+*+from+all_tab_columns@&SYNONYM_DB_LINK
+where+upper(table_name)+like+'&TARGET_TABLE_NAME'
+order+by+owner,+table_name,+column_id|code-block|syntax|javascript|4408786|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

it's never too late to respond
<pre class="lang-sql prettyprint-override"><code>select * from all_tab_columns@&amp;SYNONYM_DB_LINK
 where upper(table_name) like '&amp;TARGET_TABLE_NAME'
 order by owner, table_name, column_id
</code></pre>

blocks|key|4408638|text|select+*+from+all_tab_cols+where+table_name+in+
(select++TABLE_NAME+from+all_synonyms+where+owner+=+<SCHEMA_NAME>+)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|4408639|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select * from all_tab_cols where table_name in 
(select TABLE_NAME from all_synonyms where owner = &lt;SCHEMA_NAME&gt; )
</code></pre>

I need to get column list of SYNONYMS in ORACLE. The table on which synonym is created is in other schema. Can anyone help me in this problem?

How to get Column list of Synonym in Oracle

数据库

我需要在ORACLE中获取同义词的列列表。在其上创建同义词的表位于其他架构中。有人能帮我解决这个问题吗？

问如何获取Oracle中同义词的列列表
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取Oracle中同义词的列列表EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何获取Oracle中同义词的列列表
EN