blocks|key|1115832|text|您可以查询USER_TABLES|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1115833|select+TABLE_NAME+from+user_tables|code-block|syntax|javascript|1115834|entityMap^0|5|B|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

You can query <code>USER_TABLES</code>

<pre><code>select TABLE_NAME from user_tables
</code></pre>

blocks|key|1115893|text|试试这个，替换吗？使用您的架构名称|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1115894|select+TABLE_NAME+from++INFORMATION_SCHEMA.TABLES
+WHERE+TABLE_SCHEMA+=?
++AND+TABLE_TYPE+=+'BASE+TABLE'|code-block|syntax|javascript|1115895|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|$]]

Try this, replace ? with your schema name

<pre><code>select TABLE_NAME from INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA =?
 AND TABLE_TYPE = 'BASE TABLE'
</code></pre>

blocks|key|1330989|text|查看我的simple+utility以显示有关db模式的一些信息。它基于：Reverse+Engineering+a+Data+Model+Using+the+Oracle+Data+Dictionary|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1330990|entityMap|0|LINK|mutability|MUTABLE|url|http://code.activestate.com/recipes/576534/|1|http://www.eveandersson.com/writing/data-model-reverse-engineering^0|4|E|0|11|1T|1|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]|$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]]]

Look at my <a href="http://code.activestate.com/recipes/576534/" rel="nofollow noreferrer">simple utility</a> to show some info about db schema. It is based on: <a href="http://www.eveandersson.com/writing/data-model-reverse-engineering" rel="nofollow noreferrer">Reverse Engineering a Data Model
Using the Oracle Data Dictionary</a>

blocks|key|1330997|text|SELECT+table_name++from+all_tables+where+owner+=+'YOURSCHEMA';|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1330998|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>SELECT table_name from all_tables where owner = 'YOURSCHEMA';
</code></pre>

blocks|key|1116026|text|如果您正在使用JDBC+(Java)访问Oracle，则可以使用DatabaseMetadata类。如果您正在使用ADO.NET访问Oracle，则可以使用类似的方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1116027|如果使用ODBC访问Oracle，则可以使用SQLTables函数。|1116028|否则，如果您只需要SQLPlus或类似的Oracle客户端中的信息，那么前面提到的查询之一就可以了。例如：|1116029|select+TABLE_NAME+from+user_tables|code-block|syntax|javascript|1116030|entityMap|0|LINK|mutability|MUTABLE|url|http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.html|1|http://msdn.microsoft.com/en-us/library/ms711831(VS.85).aspx^0|W|G|0|0|M|9|1|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@$A|11|B|12|1|13]]|C|$]]|$1|F|3|G|5|6|7|14|8|@]|9|@]|C|$]]|$1|H|3|I|5|J|7|15|8|@]|9|@]|C|$K|L]]|$1|M|3|-4|5|6|7|16|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]|U|$5|P|Q|R|C|$S|V]]]]

If you are accessing Oracle with JDBC (Java) you can use <a href="http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.html" rel="nofollow noreferrer">DatabaseMetadata</a> class. If you are accessing Oracle with ADO.NET you can use a similar approach.

If you are accessing Oracle with ODBC, you can use <a href="http://msdn.microsoft.com/en-us/library/ms711831(VS.85).aspx" rel="nofollow noreferrer">SQLTables</a> function.

Otherwise, if you just need the information in SQLPlus or similar Oracle client, one of the queries already mentioned will do. For instance:

<pre><code>select TABLE_NAME from user_tables
</code></pre>

blocks|key|1116109|text|要查看其他方案中的所有表，您需要具有以下一个或多个系统权限：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1116110|SELECT+ANY+DICTIONARY
(SELECT+%7C+INSERT+%7C+UPDATE+%7C+DELETE)+ANY+TABLE|code-block|syntax|javascript|1116111|或者大锤子，数据库管理员的角色。|1116112|使用这些选项中的任意一个，您可以选择：|1116113|SELECT+DISTINCT+OWNER,+OBJECT_NAME+
++FROM+DBA_OBJECTS
+WHERE+OBJECT_TYPE+=+'TABLE'
+++AND+OWNER+=+'[some+other+schema]'|1116114|如果没有这些系统权限，您只能看到您已被授予一定级别的访问权限的表，无论是直接访问还是通过角色访问。|1116115|SELECT+DISTINCT+OWNER,+OBJECT_NAME+
++FROM+ALL_OBJECTS
+WHERE+OBJECT_TYPE+=+'TABLE'
+++AND+OWNER+=+'[some+other+schema]'|1116116|最后，您可以随时查询您自己的表的数据字典，因为您对您的表的权限不能被撤销(从10g开始)：|1116117|SELECT+DISTINCT+OBJECT_NAME+
++FROM+USER_OBJECTS
+WHERE+OBJECT_TYPE+=+'TABLE'|1116118|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|12|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|14|8|@]|9|@]|A|$E|F]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

To see all tables in another schema, you need to have one or more of the following system privileges:

<pre><code>SELECT ANY DICTIONARY
(SELECT | INSERT | UPDATE | DELETE) ANY TABLE
</code></pre>

or the big-hammer, the DBA role.

With any of those, you can select:

<pre><code>SELECT DISTINCT OWNER, OBJECT_NAME 
 FROM DBA_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
 AND OWNER = '[some other schema]'
</code></pre>

Without those system privileges, you can only see tables you have been granted some level of access to, whether directly or through a role.

<pre><code>SELECT DISTINCT OWNER, OBJECT_NAME 
 FROM ALL_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
 AND OWNER = '[some other schema]'
</code></pre>

Lastly, you can always query the data dictionary for your own tables, as your rights to your tables cannot be revoked (as of 10g):

<pre><code>SELECT DISTINCT OBJECT_NAME 
 FROM USER_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
</code></pre>

blocks|key|1331192|text|如果您以没有DBA权限的普通用户身份登录，则可以使用以下命令查看您自己的模式的所有表和视图。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1331193|select+*+from+tab;|code-block|syntax|javascript|1331194|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 logged in as Normal User without DBA permission you may uses the following command to see your own schema's all tables and views.

<pre><code>select * from tab;
</code></pre>

blocks|key|1331241|text|select+*+from+cat;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1331242|它将显示模式cat同义词user_catalog中的所有表|unstyled|1331243|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 * from cat;
</code></pre>

it will show all tables in your schema cat synonym of user_catalog

blocks|key|1331293|text|select+TABLE_NAME+from+user_tables;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1331294|上面的查询将给出该用户中存在的所有表的名称；|unstyled|1331295|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 TABLE_NAME from user_tables;
</code></pre>

Above query will give you the names of all tables present in that user;

blocks|key|1116276|text|select+*+from+user_tables;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1116277|(显示所有表格)|unstyled|1116278|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 * from user_tables;
</code></pre>

(showing all tables)

blocks|key|1116323|text|如果您还需要获取表的大小，这将是很方便的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1116324|select+SEGMENT_NAME,+PARTITION_NAME,+BYTES+from+user_segments+where+SEGMENT_TYPE='TABLE'+order+by+1|offset|length|style|CODE|1116325|entityMap^0|0|0|2R|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

If you need to get the size of the table as well, this will be handy:

<code>select SEGMENT_NAME, PARTITION_NAME, BYTES from user_segments where SEGMENT_TYPE='TABLE' order by 1</code>

blocks|key|1331423|text|SELECT+table_name，owner+FROM+all_tables+where+owner='schema_name‘order+by+table_name|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1331424|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|$]]

SELECT table_name, owner FROM all_tables where owner='schema_name' order by table_name

blocks|key|1116397|text|OWNER架构下所有表的表和行计数器的名称：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1116398|SELECT+table_name,+num_rows+counter+from+DBA_TABLES+WHERE+owner+=+'OWNER'|1116399|entityMap^0|0|5|0|0|21|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

Name of the table and rows counter for all tables under <code>OWNER</code> schema:

<code>SELECT table_name, num_rows counter from DBA_TABLES WHERE owner = 'OWNER'</code>

blocks|key|1116432|text|如果知道所有者名称，则可以直接运行第二个查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1116433|--首先，您可以选择存在的所有所有者：|1116434|SELECT+DISTINCT(owner)+from+SYS.ALL_TABLES;|code-block|syntax|javascript|1116435|--然后您可以看到该所有者的表：|1116436|SELECT+table_name,+owner+from+all_tables+where+owner+like+('%25XYZ%25');|1116437|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

You can directly run the second query if you know the owner name. 

--First you can select what all OWNERS there exist:

<pre><code>SELECT DISTINCT(owner) from SYS.ALL_TABLES;
</code></pre>

--Then you can see the tables under by that owner:

<pre><code>SELECT table_name, owner from all_tables where owner like ('%XYZ%');
</code></pre>

How do i list all tables in a schema in Oracle SQL?

How do I list all tables in a schema in Oracle SQL?

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

如何在Oracle SQL中列出模式中的所有表？

问如何在Oracle SQL中列出模式中的所有表？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle SQL中列出模式中的所有表？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle SQL中列出模式中的所有表？
EN