blocks|key|1539982|text|有趣的问题-我不认为有任何Oracle函数可以做到这一点(几乎就像Unix中的“哪个”命令)，但您可以通过以下方式获得名称的解析顺序：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1539983|select+*+from+
(
+select++object_name+objname,+object_type,+'my+object'+details,+1+resolveOrder+
++from+user_objects
++where+object_type+not+like+'SYNONYM'
+union+all
+select+synonym_name+obj+,+'my+synonym',+table_owner%7C%7C'.'%7C%7Ctable_name,+2+resolveOrder
++from+user_synonyms
+union+all
+select++synonym_name+obj+,+'public+synonym',+table_owner%7C%7C'.'%7C%7Ctable_name,+3+resolveOrder
++from+all_synonyms+where+owner+=+'PUBLIC'
)
where+objname+like+upper('&objOfInterest')|code-block|syntax|javascript|1539984|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|$]]

Interesting question - I don't think there's any Oracle function that does this (almost like a "which" command in Unix), but you can get the resolution order for the name by:

<pre><code>select * from 
(
 select object_name objname, object_type, 'my object' details, 1 resolveOrder 
 from user_objects
 where object_type not like 'SYNONYM'
 union all
 select synonym_name obj , 'my synonym', table_owner||'.'||table_name, 2 resolveOrder
 from user_synonyms
 union all
 select synonym_name obj , 'public synonym', table_owner||'.'||table_name, 3 resolveOrder
 from all_synonyms where owner = 'PUBLIC'
)
where objname like upper('&amp;objOfInterest')
</code></pre>

blocks|key|1540044|text|要在Oracle会话中查找当前用户的名称，请使用USER函数。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1540045|请注意，约束的所有者、包含外键的表的所有者以及被引用表的所有者可能都不同。这听起来像是你感兴趣的表所有者，在这种情况下，这应该是你想要的：|1540046|select+Constraint_Name
from+All_Constraints
where+Table_Name+=+'WHICHEVER_TABLE'
++and+Constraint_Type+=+'R'+and+Owner+=+User;|code-block|syntax|javascript|1540047|entityMap^0|O|4|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|S|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

To find the name of the current user within an Oracle session, use the <code>USER</code> function.

Note that the owner of the constraint, the owner of the table containing the foreign key, and the owner of the referenced table may all be different. It sounds like it&rsquo;s the table owner you&rsquo;re interested in, in which case this should be close to what you want:

<pre><code>select Constraint_Name
from All_Constraints
where Table_Name = 'WHICHEVER_TABLE'
 and Constraint_Type = 'R' and Owner = User;
</code></pre>

blocks|key|1324991|text|像ALL_TABLES和ALL_CONSTRAINTS这样的Oracle视图有一个owner列，您可以使用它来限制查询。这些表也有以user开头而不是ALL开头的变体，这些表只列出当前用户可以访问的对象。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1324992|其中一个these视图应该有助于解决您的问题。对于类似的问题，它们对我来说总是工作得很好。|offset|length|1324993|entityMap|0|LINK|mutability|MUTABLE|url|http://www.techonthenet.com/oracle/sys_tables/index.php^0|0|4|5|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

Oracle views like ALL_TABLES and ALL_CONSTRAINTS have an owner column, which you can use to restrict your query. There are also variants of these tables beginning with USER instead of ALL, which only list objects which can be accessed by the current user.

One of <a href="http://www.techonthenet.com/oracle/sys_tables/index.php" rel="nofollow noreferrer">these</a> views should help to solve your problem. They always worked fine for me for similar problems.

blocks|key|1325056|text|当我在Googling上搜索如何在Oracle中找到表的所有者时，我发现这个问题是最好的结果，所以我想我应该提供一个表特定的答案，以方便其他人。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1325057|要查找Oracle+DB中特定表的所有者，请使用以下查询：|1325058|select+owner+from+ALL_TABLES+where+TABLE_NAME+='<MY-TABLE-NAME>';|code-block|syntax|javascript|1325059|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I found this question as the top result while Googling how to find the owner of a table in Oracle, so I thought that I would contribute a table specific answer for others' convenience.

To find the owner of a specific table in an Oracle DB, use the following query:

<pre><code>select owner from ALL_TABLES where TABLE_NAME ='&lt;MY-TABLE-NAME&gt;';
</code></pre>

blocks|key|1540264|text|这就像@entpnerd所说的，但我建议你使用use+()子句：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1540265|select+*+
from+ALL_TABLES+
where+upper(TABLE_NAME)+=+upper('<table_name>')|code-block|syntax|javascript|1540266|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 is like @entpnerd said, but I suggest you to use upper() clause:
<pre><code>select * 
from ALL_TABLES 
where upper(TABLE_NAME) = upper('&lt;table_name&gt;')
</code></pre>

I want to find the foreign keys of a table but there may be more than one user / schema with a table with the same name. How can I find the one that the currently logged user is seeing? Is there a function that gives its owner? What if there are public synonyms?

How can I find the OWNER of an object in Oracle?

Unix

我想查找表的外键，但可能有多个用户/模式具有同名的表。如何找到当前登录的用户正在查看的内容？有没有一个函数可以给它的所有者呢？如果有公共同义词怎么办？

问如何在Oracle中查找对象的所有者？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle中查找对象的所有者？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle中查找对象的所有者？
EN