获取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_TYPE
的"TABLE"的物化视图日志的信息,这不太可能是您真正想要的. 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屋!