如何在Oracle SQL中列出架构中的所有表? [英] How do I list all tables in a schema in Oracle SQL?

问题描述

如何在Oracle SQL中列出架构中的所有表?

How do i list all tables in a schema in Oracle SQL?

推荐答案

要查看另一模式中的所有表，您需要具有以下一个或多个系统特权:

To see all tables in another schema, you need to have one or more of the following system privileges:

SELECT ANY DICTIONARY
(SELECT | INSERT | UPDATE | DELETE) ANY TABLE

或者是DBA角色.

使用这些中的任何一个，您都可以选择:

With any of those, you can select:

SELECT DISTINCT OWNER, OBJECT_NAME 
  FROM DBA_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
   AND OWNER = '[some other schema]'

没有这些系统特权，您只能看到直接或通过角色被授予某种级别的访问权限的表.

Without those system privileges, you can only see tables you have been granted some level of access to, whether directly or through a role.

SELECT DISTINCT OWNER, OBJECT_NAME 
  FROM ALL_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
   AND OWNER = '[some other schema]'

最后，您始终可以查询自己的表的数据字典，因为对表的权限无法撤消(从10g开始):

Lastly, you can always query the data dictionary for your own tables, as your rights to your tables cannot be revoked (as of 10g):

SELECT DISTINCT OBJECT_NAME 
  FROM USER_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'

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