如何在Oracle中找到对象的所有者? [英] How can I find the OWNER of an object in 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?

推荐答案

您可以查询ALL_OBJECTS视图:

You can query the ALL_OBJECTS view:

select owner
     , object_name
     , object_type
  from ALL_OBJECTS
 where object_name = 'FOO'

要查找同义词:

select *
  from ALL_SYNONYMS
 where synonym_name = 'FOO'

仅需澄清一下，如果 user 用户的SQL语句引用了没有模式限定(例如'FOO')的对象名称，则Oracle FIRST会在用户的模式中检查该名称的对象(包括同义词)在该用户的模式中).如果Oracle无法从用户的架构中解析引用，则Oracle将检查是否有公共同义词.

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.

如果您要特别查找对特定table_name的约束:

If you are looking specifically for constraints on a particular 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

-附录:

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

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.

这篇关于如何在Oracle中找到对象的所有者?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！