如何在 Oracle SQL 中列出模式中的所有表?

2021-12-06 00:00:00 sql oracle

如何在 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 角色.

or the big-hammer, the DBA role.

您可以选择其中任何一个:

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'

相关文章