如何在 Oracle 中找到对象的所有者？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/947140/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I find the OWNER of an object in Oracle?
提问by spencer7593
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?
我想查找表的外键，但可能有多个用户/模式具有相同名称的表。如何找到当前登录用户看到的那个？有没有一个函数可以给它的所有者？如果有公共同义词怎么办？
回答by spencer7593
You can query the ALL_OBJECTS view:
您可以查询 ALL_OBJECTS 视图：
select owner
, object_name
, object_type
from ALL_OBJECTS
where object_name = 'FOO'
To find synonyms:
查找同义词：
select *
from ALL_SYNONYMS
where synonym_name = 'FOO'
Just to clarify, if a useruser's SQL statement references an object name with no schema qualification (e.g. 'FOO'), Oracle FIRST checks the user's schema for an object of that name (including synonyms in that user's schema). If Oracle can't resolve the reference from the user's schema, Oracle then checks for a public synonym.
只是为了澄清，如果用户用户的SQL语句引用，没有模式修饰（如“富”），Oracle首先检查该名称的对象的用户的模式中的对象名称（包括该用户的模式同义词）。如果 Oracle 无法解析来自用户模式的引用，则 Oracle 将检查公共同义词。
If you are looking specifically for constraints on a particular table_name:
如果您正在寻找特定 table_name 的约束：
select c.*
from all_constraints c
where c.table_name = 'FOO'
union all
select cs.*
from all_constraints cs
join all_synonyms s
on (s.table_name = cs.table_name
and s.table_owner = cs.owner
and s.synonym_name = 'FOO'
)
HTH
HTH
-- addendum:
-- 附录：
If your user is granted access to the DBA_ views (e.g. if your user has been granted SELECT_CATALOG_ROLE), you can substitute 'DBA_' in place of 'ALL_' in the preceding SQL examples. The ALL_xviews only show objects which you have been granted privileges. The DBA_xviews will show all database objects, whether you have privileges on them or not.
如果您的用户被授予对 DBA_ 视图的访问权限（例如，如果您的用户被授予 SELECT_CATALOG_ROLE），您可以在前面的 SQL 示例中用“ DBA_”代替“ ALL_”。该ALL_x观点只显示你已经授予的权限对象。该DBA_x意见将显示所有数据库对象，你是否对他们还是不特权。
回答by dpbradley
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:
有趣的问题 - 我认为没有任何 Oracle 函数可以执行此操作（几乎类似于 Unix 中的“which”命令），但是您可以通过以下方式获取名称的解析顺序：
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('&objOfInterest')
回答by cheduardo
To find the name of the current user within an Oracle session, use the USERfunction.
要在 Oracle 会话中查找当前用户的名称，请使用该USER函数。
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’s the table owner you’re interested in, in which case this should be close to what you want:
请注意，约束的所有者、包含外键的表的所有者和引用表的所有者可能都不同。这听起来像是您感兴趣的表所有者，在这种情况下，这应该接近您想要的：
select Constraint_Name
from All_Constraints
where Table_Name = 'WHICHEVER_TABLE'
and Constraint_Type = 'R' and Owner = User;
回答by Karl Bartel
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.
像 ALL_TABLES 和 ALL_CONSTRAINTS 这样的 Oracle 视图有一个所有者列，您可以使用它来限制您的查询。这些表也有以 USER 而不是 ALL 开头的变体，它们只列出当前用户可以访问的对象。
One of theseviews should help to solve your problem. They always worked fine for me for similar problems.
其中的这些意见应有助于解决您的问题。对于类似的问题，他们对我来说总是很好。
回答by entpnerd
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.
在谷歌搜索如何在 Oracle 中查找表的所有者时，我发现这个问题是最重要的结果，所以我想我会为其他人提供一个特定于表的答案以方便其他人。
To find the owner of a specific table in an Oracle DB, use the following query:
要查找 Oracle DB 中特定表的所有者，请使用以下查询：
select owner from ALL_TABLES where TABLE_NAME ='<MY-TABLE-NAME>';
