计算 Oracle 中的表数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5062809/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count number of tables in Oracle
提问by SuperMan
Like MySQL has "SHOW TABLES" , how do you count tables in Oracle DB. Little bit of research gave me this query:
就像 MySQL 有 "SHOW TABLES" 一样，你如何计算 Oracle DB 中的表。一点点研究给了我这个查询：
select owner, count(*) from dba_tables
So like MySQL has a standard command, does Oracle have one?
所以就像 MySQL 有一个标准命令一样，Oracle 有一个吗？
回答by akf
回答by Shekhar_Pro
Yeah sure your query will work just modify it a little. Look here for refrence : http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091
是的，您的查询将起作用，只需稍微修改一下即可。在这里查看参考：http: //download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091
Run this:
运行这个：
SELECT TABLE_NAME FROM DBA_TABLES;
to get list of tables.
获取表格列表。
and Run this:
并运行这个：
SELECT Count(*) FROM DBA_TABLES;
to get the count of tables.
获取表的数量。
回答by Rakesh Anand
Select count(*) FROM all_tables where owner='schema_name'
Select count(*) FROM all_tables where owner='schema_name'
回答by I?ja
These documents describe data dictionary views:
这些文档描述了数据字典视图：
all_tables: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4473.htm#REFRN26286
all_tables：http: //docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4473.htm#REFRN26286
user_tables: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091
用户表：http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091
dba_tables: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4155.htm#i1627762
dba_tables：http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4155.htm#i1627762
You can run queries on these views to count what you need.
您可以对这些视图运行查询以计算您需要的内容。
To add something more to @Anurag Thakre's answer:
为@Anurag Thakre 的回答添加更多内容：
Use this query which will give you the actual no of counts respect to the owners
SELECT COUNT(*),tablespace_name FROM USER_TABLES group by tablespace_name;
使用此查询将为您提供对所有者的实际计数
SELECT COUNT(*),tablespace_name FROM USER_TABLES group by tablespace_name;
Or by table owners:
或由表所有者：
SELECT COUNT(*), owner FROM ALL_TABLES group by owner;
Tablespace itself does not identify an unique object owner. Multiple users can create objects in the same tablespace and a single user can create objects in various tablespaces. It is a common practice to separate tables and indexes into different tablespaces.
表空间本身不标识唯一的对象所有者。多个用户可以在同一个表空间中创建对象，一个用户可以在不同的表空间中创建对象。将表和索引分离到不同的表空间中是一种常见的做法。
回答by Ajay
Please find below - its the simplest one I use :
请在下面找到 - 它是我使用的最简单的一个：
select owner, count(*) from dba_tables group by owner;
回答by David W
If you'd like a list of owners, and the count of the number of tables per owner, try:
如果您想要所有者列表以及每个所有者的表数，请尝试：
SELECT distinct owner, count(table_name) FROM dba_tables GROUP BY owner;
回答by jovan
If you want to know the number of tables that belong to a certain schema/user, you can also use SQL similar to this one:
如果你想知道属于某个模式/用户的表的数量，你也可以使用类似于这个的 SQL：
SELECT Count(*) FROM DBA_TABLES where OWNER like 'PART_OF_NAME%';
回答by Girish
REM setting current_schema is required as the 2nd query depends on the current user referred in the session
ALTER SESSION SET CURRENT_SCHEMA=TABLE_OWNER;
SELECT table_name,
TO_NUMBER (
EXTRACTVALUE (
xmltype (
DBMS_XMLGEN.getxml ('select count(*) c from ' || table_name)),
'/ROWSET/ROW/C'))
COUNT
FROM dba_tables
WHERE owner = 'TABLE_OWNER'
ORDER BY COUNT DESC;
回答by Anurag Thakre
Use this query which will give you the actual no of counts in respect to the table owners
使用此查询将为您提供有关表所有者的实际计数
SELECT COUNT(*),tablespace_name FROM USER_TABLES group by tablespace_name;
回答by Lotus
select COUNT(*) from ALL_ALL_TABLES where OWNER='<Database-name>';
.....
.....
