如何查询Oracle数据库以显示Oracle数据库中所有表的名称?


当前回答

执行以下命令:

显示Oracle数据库中的所有表

SELECT table_name FROM dba_tables;

显示当前用户拥有的表

SELECT table_name FROM user_tables;

显示当前用户可以访问的表

sql> SELECT table_name FROM all_tables

其他回答

select * from dba_tables

仅当您登录的用户具有sysdba权限时,才提供所有用户的所有表。

包括视图:

SELECT owner, table_name as table_view
  FROM dba_tables
UNION ALL
SELECT owner, view_name as table_view
  FROM DBA_VIEWS

下面的查询只列出了所需的数据,而其他的答案给了我额外的数据,这只会让我感到困惑。

select table_name from user_tables;

Oracle数据库显示所有表的名称使用下面的查询

SELECT owner, table_name FROM dba_tables;

SELECT owner, table_name FROM all_tables;

SELECT table_name FROM user_tables;

访问更多信息:http://www.plsqlinformation.com/2016/08/get-list-of-all-tables-in-oracle.html

SELECT owner, table_name
  FROM dba_tables

This is assuming that you have access to the DBA_TABLES data dictionary view. If you do not have those privileges but need them, you can request that the DBA explicitly grants you privileges on that table, or, that the DBA grants you the SELECT ANY DICTIONARY privilege or the SELECT_CATALOG_ROLE role (either of which would allow you to query any data dictionary table). Of course, you may want to exclude certain schemas like SYS and SYSTEM which have large numbers of Oracle tables that you probably don't care about.

或者,如果您没有访问DBA_TABLES,您可以通过ALL_TABLES视图查看您的帐户访问的所有表:

SELECT owner, table_name
  FROM all_tables

不过,这可能是数据库中可用表的一个子集(ALL_TABLES显示用户已被授予访问权限的所有表的信息)。

如果你只关心你拥有的表,而不是那些你可以访问的表,你可以使用USER_TABLES:

SELECT table_name
  FROM user_tables

因为USER_TABLES只包含关于您拥有的表的信息,所以它没有OWNER列——根据定义,所有者就是您自己。

Oracle also has a number of legacy data dictionary views-- TAB, DICT, TABS, and CAT for example-- that could be used. In general, I would not suggest using these legacy views unless you absolutely need to backport your scripts to Oracle 6. Oracle has not changed these views in a long time so they often have problems with newer types of objects. For example, the TAB and CAT views both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLES views all filter those out. CAT also shows information about materialized view logs with a TABLE_TYPE of "TABLE" which is unlikely to be what you really want. DICT combines tables and synonyms and doesn't tell you who owns the object.