如何在Oracle中找到对象的所有者？

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?

我想找到一个表的外键,但可能有多个用户/模式与一个具有相同名称的表。如何找到当前登录用户看到的那个?有没有给它的主人的功能?如果有公共同义词怎么办?

5 个解决方案

#1

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 ~~user~~ user'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语句引用没有模式限定的对象名称(例如“FOO”),Oracle FIRST会检查用户的模式以查找该名称的对象(包括该用户模式中的同义词)。如果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

-- 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_x views only show objects which you have been granted privileges. The DBA_x views will show all database objects, whether you have privileges on them or not.

如果您的用户被授予访问DBA_视图的权限(例如,如果您的用户已被授予SELECT_CATALOG_ROLE),则可以在前面的SQL示例中用'DBA_'代替'ALL_'。 ALL_x视图仅显示您已被授予权限的对象。 DBA_x视图将显示所有数据库对象,无论您是否拥有它们的权限。

#2

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中的“哪个”命令),但你可以通过以下方式获得名称的解析顺序:

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')

#3

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 these views should help to solve your problem. They always worked fine for me for similar problems.

其中一个视图应该有助于解决您的问题。对于类似的问题,他们总是对我很好。

#4

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

要在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;

#5

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>';

#1