SQL 获取 Oracle 中所有表的列表？

Get list of all tables in Oracle?

提问by vitule

How do I query an Oracle database to display the names of all tables in it?

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

回答by Justin Cave

SELECT owner, table_name
  FROM dba_tables

This is assuming that you have access to the DBA_TABLESdata 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 DICTIONARYprivilege or the SELECT_CATALOG_ROLErole (either of which would allow you to query any data dictionary table). Of course, you may want to exclude certain schemas like SYSand SYSTEMwhich have large numbers of Oracle tables that you probably don't care about.

这是假设您有权访问DBA_TABLES数据字典视图。如果您没有这些权限但需要它们，您可以请求 DBA 明确授予您对该表的权限，或者 DBA 授予您SELECT ANY DICTIONARY权限或SELECT_CATALOG_ROLE角色（两者都允许您查询任何数据字典表） ）。当然，你可能想要排除某些模式像SYS和SYSTEM它有大量的Oracle表的，你可能不关心。

Alternatively, if you do not have access to DBA_TABLES, you can see all the tables that your account has access to through the ALL_TABLESview:

或者，如果您无权访问DBA_TABLES，您可以通过ALL_TABLES视图查看您的帐户有权访问的所有表：

SELECT owner, table_name
  FROM all_tables

Although, that may be a subset of the tables available in the database (ALL_TABLESshows you the information for all the tables that your user has been granted access to).

虽然，这可能是数据库中可用表的子集（ALL_TABLES显示您的用户已被授予访问权限的所有表的信息）。

If you are only concerned with the tables that you own, not those that you have access to, you could use USER_TABLES:

如果您只关心您拥有的表，而不是您有权访问的表，则可以使用USER_TABLES：

SELECT table_name
  FROM user_tables

Since USER_TABLESonly has information about the tables that you own, it does not have an OWNERcolumn – the owner, by definition, is you.

由于USER_TABLES只有有关您拥有的表的信息，因此它没有OWNER列 – 根据定义，所有者就是您。

Oracle also has a number of legacy data dictionary views-- TAB, DICT, TABS, and CATfor 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 TABand CATviews both show information about tables that are in the user's recycle bin while the [DBA|ALL|USER]_TABLESviews all filter those out. CATalso shows information about materialized view logs with a TABLE_TYPEof "TABLE" which is unlikely to be what you really want. DICTcombines tables and synonyms and doesn't tell you who owns the object.

甲骨文也有一些遗留的数据字典views-- TAB，DICT，TABS，并CAT为example--可能被使用。一般而言，除非您绝对需要将脚本向后移植到 Oracle 6，否则我不建议使用这些旧视图。 Oracle 很长时间没有更改这些视图，因此它们在处理较新类型的对象时经常会出现问题。例如，TAB和CAT视图都显示有关用户回收站中表的信息，而[DBA|ALL|USER]_TABLES视图都将它们过滤掉。 CAT还显示有关带有TABLE_TYPE“TABLE”的物化视图日志的信息，这不太可能是您真正想要的。 DICT结合了表和同义词，并且不会告诉您谁拥有该对象。

回答by vitule

Querying user_tablesand dba_tablesdidn't work.
This one did:

查询user_tables并dba_tables没有工作。
这个做到了：

select table_name from all_tables  

回答by stealth_angoid

Going one step further, there is another view called cols (all_tab_columns) which can be used to ascertain which tables contain a given column name.

更进一步，还有另一个名为 cols (all_tab_columns) 的视图，可用于确定哪些表包含给定的列名。

For example:

例如：

SELECT table_name, column_name
FROM cols
WHERE table_name LIKE 'EST%'
AND column_name LIKE '%CALLREF%';

to find all tables having a name beginning with EST and columns containing CALLREF anywhere in their names.

查找名称以 EST 开头的所有表以及名称中任何位置包含 CALLREF 的列。

This can help when working out what columns you want to join on, for example, depending on your table and column naming conventions.

例如，根据您的表和列命名约定，这在确定要加入的列时会有所帮助。

回答by cwd

For better viewing with sqlplus

为了更好地观看 sqlplus

If you're using sqlplusyou may want to first set up a few parameters for nicer viewing if your columns are getting mangled (these variables should not persist after you exit your sqlplussession ):

如果您正在使用，sqlplus您可能希望首先设置一些参数，以便在您的列被损坏时更好地查看（这些变量在您退出sqlplus会话后不应保留）：

set colsep '|'
set linesize 167
set pagesize 30
set pagesize 1000

Show All Tables

显示所有表格

You can then use something like this to see all table names:

然后你可以使用这样的东西来查看所有表名：

SELECT table_name, owner, tablespace_name FROM all_tables;

Show Tables You Own

显示您拥有的表

As @Justin Cave mentions, you can use this to show only tables that you own:

正如@Justin Cave 提到的，您可以使用它来仅显示您拥有的表：

SELECT table_name FROM user_tables;

Don't Forget about Views

不要忘记视图

Keep in mind that some "tables" may actually be "views" so you can also try running something like:

请记住，某些“表”实际上可能是“视图”，因此您也可以尝试运行以下内容：

SELECT view_name FROM all_views;

The Results

结果

This should yield something that looks fairly acceptable like:

这应该会产生一些看起来相当可以接受的东西，例如：

回答by Israel Margulies

Simple query to select the tables for the current user:

为当前用户选择表的简单查询：

  SELECT table_name FROM user_tables;

回答by Harshil

    select object_name from user_objects where object_type='TABLE';

----------------OR------------------

- - - - - - - - 或者 - - - - - - - - -

    select * from tab;

----------------OR------------------

- - - - - - - - 或者 - - - - - - - - -

    select table_name from user_tables;

回答by Mahmoud Ahmed El-Sayed

Try the below data dictionary views.

试试下面的数据字典视图。

tabs
dba_tables
all_tables
user_tables

回答by Eddie Awad

Try selecting from user_tableswhich lists the tables owned by the current user.

尝试从user_tables中选择，其中列出了当前用户拥有的表。

回答by Brahmareddy K

Oracle database to display the names of all tables using below query

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

SELECT owner, table_name FROM dba_tables;

SELECT owner, table_name FROM all_tables;

SELECT table_name FROM user_tables;

vist more : http://www.plsqlinformation.com/2016/08/get-list-of-all-tables-in-oracle.html

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

回答by Van Gogh

With any of those, you can select:

对于其中任何一个，您可以选择：

SELECT DISTINCT OWNER, OBJECT_NAME 
    FROM DBA_OBJECTS 
    WHERE OBJECT_TYPE = 'TABLE' AND OWNER='SOME_SCHEMA_NAME';

SELECT DISTINCT OWNER, OBJECT_NAME 
    FROM ALL_OBJECTS 
    WHERE OBJECT_TYPE = 'TABLE' AND OWNER='SOME_SCHEMA_NAME';