blocks|key|4765282|text|当然，只要稍微修改一下，您的查询就能正常工作。在这里查找参考：http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|4765283|运行以下命令：|4765284|SELECT+TABLE_NAME+FROM+DBA_TABLES;|code-block|syntax|javascript|4765285|以获取表的列表。|4765286|然后运行以下命令：|4765287|SELECT+Count(*)+FROM+DBA_TABLES;|4765288|以获取表的计数。|4765289|entityMap|0|LINK|mutability|MUTABLE|url|http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091^0|V|2I|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|D|3|E|5|6|7|14|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|15|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|16|8|@]|9|@]|C|$]]|$1|M|3|N|5|6|7|17|8|@]|9|@]|C|$]]|$1|O|3|P|5|H|7|18|8|@]|9|@]|C|$I|J]]|$1|Q|3|R|5|6|7|19|8|@]|9|@]|C|$]]|$1|S|3|-4|5|6|7|1A|8|@]|9|@]|C|$]]]|T|$U|$5|V|W|X|C|$Y|Z]]]]

Yeah sure your query will work just modify it a little. Look here for refrence : <a href="http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091" rel="noreferrer">http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091</a>

Run this:

<pre><code>SELECT TABLE_NAME FROM DBA_TABLES;
</code></pre>

to get list of tables.

and Run this:

<pre><code>SELECT Count(*) FROM DBA_TABLES;
</code></pre>

to get the count of tables.

blocks|key|1098519|text|尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1098520|SELECT+COUNT(*)+FROM+USER_TABLES;|code-block|syntax|javascript|1098521|1098522|我的机器上没有oracle，我运行的是mysql+(操作注释)|blockquote|1098523|1098524|1098525|在撰写本文时，this+site非常适合在各种类型的数据库上进行测试。|offset|length|1098526|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlzoo.net/howto/source/z.dir/tip318780/oracle^0|0|0|0|0|0|0|7|9|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|10|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|11|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|12|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|13|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|14|8|@]|9|@$O|15|P|16|1|17]]|A|$]]|$1|Q|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

try:

<pre><code>SELECT COUNT(*) FROM USER_TABLES;
</code></pre>

<blockquote>
 Well i dont have oracle on my machine, i run mysql (OP comment)
</blockquote>

at the time of writing, <a href="http://sqlzoo.net/howto/source/z.dir/tip318780/oracle" rel="noreferrer">this site</a> was great for testing on a variety of database types.

blocks|key|1898892|text|从all_tables+where+owner='schema_name'选择计数(*)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1898893|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

<blockquote>
 Select count(*) FROM all_tables where owner='schema_name'
</blockquote>

blocks|key|1098659|text|这些文档描述了数据字典视图：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1098660|all_tables：http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4473.htm#REFRN26286|offset|length|1098661|user_tables：http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091|1098662|dba_tables：http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4155.htm#i1627762|1098663|您可以在这些视图上运行查询来计算您需要的内容。|1098664|在@Anurag+Thakre的回答中添加更多内容：|1098665|1098666|使用以下查询，它将向您提供关于所有者的实际计数数|blockquote|1098667|1098668|SELECT+COUNT(*)，tablespace_name+FROM+USER_TABLES+group+by+tablespace_name；|1098669|1098670|或由表所有者执行：|1098671|SELECT+COUNT(*),+owner++FROM+ALL_TABLES+group+by+owner;|code-block|syntax|javascript|1098672|表空间本身并不标识唯一的对象所有者。多个用户可以在同一个表空间中创建对象，单个用户可以在不同的表空间中创建对象。将表和索引分隔到不同的表空间中是一种常见的做法。|1098673|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4473.htm#REFRN26286|1|http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091|2|http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4155.htm#i1627762^0|0|B|2B|0|0|C|29|1|0|B|29|2|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1H|8|@]|9|@$D|1I|E|1J|1|1K]]|A|$]]|$1|F|3|G|5|6|7|1L|8|@]|9|@$D|1M|E|1N|1|1O]]|A|$]]|$1|H|3|I|5|6|7|1P|8|@]|9|@$D|1Q|E|1R|1|1S]]|A|$]]|$1|J|3|K|5|6|7|1T|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1U|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|1V|8|@]|9|@]|A|$]]|$1|O|3|P|5|Q|7|1W|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1X|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1Z|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|20|8|@]|9|@]|A|$]]|$1|X|3|Y|5|Z|7|21|8|@]|9|@]|A|$10|11]]|$1|12|3|13|5|6|7|22|8|@]|9|@]|A|$]]|$1|14|3|-4|5|6|7|23|8|@]|9|@]|A|$]]]|15|$16|$5|17|18|19|A|$1A|1B]]|1C|$5|17|18|19|A|$1A|1D]]|1E|$5|17|18|19|A|$1A|1F]]]]

These documents describe data dictionary views:

all_tables: <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4473.htm#REFRN26286" rel="nofollow">http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4473.htm#REFRN26286</a>

user_tables: <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091" rel="nofollow">http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091</a>

dba_tables: <a href="http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4155.htm#i1627762" rel="nofollow">http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4155.htm#i1627762</a>

You can run queries on these views to count what you need.

To add something more to @Anurag Thakre's answer:

<blockquote>
 Use this query which will give you the actual no of counts respect to
 the owners

<pre><code>SELECT COUNT(*),tablespace_name FROM USER_TABLES group by tablespace_name;
</code></pre>
</blockquote>

Or by table owners:

<pre><code>SELECT COUNT(*), owner FROM ALL_TABLES group by owner;
</code></pre>

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.

blocks|key|1098833|text|请找到下面-这是我使用的最简单的一个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1098834|select+owner,+count(*)+from+dba_tables+group+by+owner;|code-block|syntax|javascript|1098835|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Please find below - its the simplest one I use :

<pre><code>select owner, count(*) from dba_tables group by owner;
</code></pre>

blocks|key|1898926|text|如果您想要所有者列表，以及每个所有者的表数，请尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1898927|++++SELECT+distinct+owner,+count(table_name)+FROM+dba_tables+GROUP+BY+owner;|code-block|syntax|javascript|1898928|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you'd like a list of owners, and the count of the number of tables per owner, try:

<pre><code> SELECT distinct owner, count(table_name) FROM dba_tables GROUP BY owner;
</code></pre>

blocks|key|4765589|text|如果您想知道属于某个模式/用户的表数，也可以使用类似下面的SQL：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4765590|SELECT+Count(*)+FROM+DBA_TABLES+where+OWNER+like+'PART_OF_NAME%25';|code-block|syntax|javascript|4765591|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>SELECT Count(*) FROM DBA_TABLES where OWNER like 'PART_OF_NAME%';
</code></pre>

blocks|key|1098825|text|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+'+%7C%7C+table_name)),
+++++++++++++++'/ROWSET/ROW/C'))
++++++++++++COUNT
++++FROM+dba_tables
+++WHERE+owner+=+'TABLE_OWNER'
ORDER+BY+COUNT+DESC;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1098826|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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;
</code></pre>

blocks|key|3165093|text|使用这个查询，它将给出表所有者的实际计数数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3165094|SELECT+COUNT(*),tablespace_name++FROM+USER_TABLES+group+by+tablespace_name;|code-block|syntax|javascript|3165095|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Use this query which will give you the actual no of counts in respect to the table owners

<pre><code>SELECT COUNT(*),tablespace_name FROM USER_TABLES group by tablespace_name;
</code></pre>

blocks|key|4765383|text|select+COUNT(*)+from+ALL_ALL_TABLES+where+OWNER='<Database-name>';|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|4765384|.|unstyled|4765385|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>select COUNT(*) from ALL_ALL_TABLES where OWNER='&lt;Database-name&gt;';
</code></pre>

.....

blocks|key|3165228|text|此查询将查找特定方案中的所有对象计数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3165229|select+owner,+object_type,+count(*)+from+dba_objects+where+owner='owner_name'+group+by+owner,+object_type+order+by+3+desc;|code-block|syntax|javascript|3165230|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

THIS QUERY WILL FIND ALL THE OBJECTS COUNTS IN A SPECIFIC SCHEMA

<pre><code>select owner, object_type, count(*) from dba_objects where owner='owner_name' group by owner, object_type order by 3 desc;
</code></pre>

Like MySQL has "SHOW TABLES" , how do you count tables in Oracle DB. Little bit of research gave me this query:

<pre><code>select owner, count(*) from dba_tables
</code></pre>

So like MySQL has a standard command, does Oracle have one?

Count number of tables in Oracle

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

就像MySQL有"SHOW TABLES“一样，如何在Oracle DB中对表进行计数。一些研究给了我这样的疑问：select owner, count(*) from dba_tables那么，就像MySQL有一个标准命令一样，Oracle也有一个标准命令吗？

问计算Oracle中的表数
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问计算Oracle中的表数EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问计算Oracle中的表数
EN