获取 Oracle 中所有表的列表? [英] Get list of all tables in Oracle?

问题描述

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

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

推荐答案

SELECT owner, table_name
  FROM dba_tables

这假设您有权访问DBA_TABLES 数据字典视图.如果您没有这些权限但需要它们，您可以请求 DBA 明确授予您对该表的权限，或者 DBA 授予您 SELECT ANY DICTIONARY 权限或 SELECT_CATALOG_ROLE 角色(其中任何一个都允许您查询任何数据字典表).当然，您可能希望排除某些模式，例如 SYS 和 SYSTEM，它们具有大量您可能不关心的 Oracle 表.

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 视图查看您的帐户有权访问的所有表:

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_TABLES view:

SELECT owner, table_name
  FROM all_tables

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

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

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

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

SELECT table_name
  FROM user_tables

由于 USER_TABLES 仅包含有关您拥有的表的信息，因此它没有 OWNER 列 - 根据定义，所有者就是您.

Since USER_TABLES only has information about the tables that you own, it does not have an OWNER column – the owner, by definition, is you.

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

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.

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