blocks|key|1482852|text|IIRC您需要的表有DBA_TABLES、DBA_EXTENTS或DBA_SEGMENTS和DBA_DATA_FILES。如果您在计算机上没有管理权限，还可以看到这些表的USER_和ALL_版本。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1482853|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|$]]

IIRC the tables you need are DBA_TABLES, DBA_EXTENTS or DBA_SEGMENTS and DBA_DATA_FILES. There are also USER_ and ALL_ versions of these for tables you can see if you don't have administration permissions on the machine.

blocks|key|1698113|text|首先，我通常要注意的是，为了进行空间分析而收集表统计数据是一件潜在的危险事情。收集统计信息可能会更改查询计划，尤其是在DBA配置了一个使用调用未使用的非默认参数的统计信息收集作业的情况下，这将导致Oracle重新解析使用相关表的查询，这可能会影响性能。如果数据库管理员故意让某些表没有统计信息(如果选择了OPTIMIZER_MODE，则通常是这样)，收集统计信息可能会导致Oracle停止使用基于规则的优化器，而开始为一组查询使用基于成本的优化器，如果在生产中意外执行，这将是一个主要的性能难题。如果您的统计数据准确，则可以直接查询USER_TABLES+(或ALL_TABLES或DBA_TABLES)，而无需调用GATHER_TABLE_STATS。如果你的统计数据不准确，可能是有原因的，你不想破坏现状。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1698114|其次，与SQL+Server+sp_spaceused过程最接近的等价物可能是Oracle的DBMS_SPACE包。Tom+Kyte有一个很好的show_space+procedure，它为这个包提供了一个简单的接口，并打印出类似于sp_spaceused打印的信息。|1698115|entityMap|0|LINK|mutability|MUTABLE|url|http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:231414051079^0|48|E|7E|B|7S|A|83|A|8K|I|0|F|C|1A|A|20|A|38|C|20|K|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@$9|Q|A|R|B|C]|$9|S|A|T|B|C]|$9|U|A|V|B|C]|$9|W|A|X|B|C]|$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|C]|$9|15|A|16|B|C]|$9|17|A|18|B|C]]|D|@$9|19|A|1A|1|1B]]|E|$]]|$1|H|3|-4|5|6|7|1C|8|@]|D|@]|E|$]]]|I|$J|$5|K|L|M|E|$N|O]]]]

First off, I would generally caution that gathering table statistics in order to do space analysis is a potentially dangerous thing to do. Gathering statistics may change query plans, particularly if the DBA has configured a statistics gathering job that uses non-default parameters that your call is not using, and will cause Oracle to re-parse queries that utilize the table in question which can be a performance hit. If the DBA has intentionally left some tables without statistics (common if your <code>OPTIMIZER_MODE</code> is CHOOSE), gathering statistics can cause Oracle to stop using the rule-based optimizer and start using the cost-based optimizer for a set of queries which can be a major performance headache if it is done unexpectedly in production. If your statistics are accurate, you can query <code>USER_TABLES</code> (or <code>ALL_TABLES</code> or <code>DBA_TABLES</code>) directly without calling <code>GATHER_TABLE_STATS</code>. If your statistics are not accurate, there is probably a reason for that and you don't want to disturb the status quo.

Second, the closest equivalent to the SQL Server <code>sp_spaceused</code> procedure is likely Oracle's <code>DBMS_SPACE</code> package. Tom Kyte has a nice <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:231414051079" rel="noreferrer"><code>show_space</code> procedure</a> that provides a simple interface to this package and prints out information similar to what <code>sp_spaceused</code> prints out.

blocks|key|1482977|text|这取决于你所说的“表的大小”。表与文件系统上的特定文件无关。一个表将驻留在一个表空间中(如果它已分区，则可能存在多个表空间；如果还希望考虑表上的索引，则可能存在多个表空间)。一个表空间通常包含多个表，并且可能分布在多个文件中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1482978|如果您正在估计表的未来增长将需要多少空间，那么avg_row_len乘以表中的行数(或您期望的表中的行数)将是一个很好的指南。但Oracle将在每个块上保留一些空闲空间，部分原因是允许行在更新时“增长”，部分原因是可能无法在该块上容纳另一整行(例如，一个8K块只能容纳2行3K，尽管这是一个极端的例子，因为3K比大多数行大得多)。因此，块(在USER_TABLES中)可能是更好的指南。|1482979|但是，如果一个表中有200,000行，删除了其中的一半，那么该表仍然“拥有”相同数量的块。它不会释放它们以供其他表使用。此外，块不是单独添加到表中，而是以组的形式添加到称为“区段”的组中。因此，表中通常会有EMPTY_BLOCKS+(也是在USER_TABLES中)。|1482980|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Depends what you mean by "table's size". 
A table doesn't relate to a specific file on the file system. A table will reside on a tablespace (possibly multiple tablespaces if it is partitioned, and possibly multiple tablespaces if you also want to take into account indexes on the table).
A tablespace will often have multiple tables in it, and may be spread across multiple files.

If you are estimating how much space you'll need for the table's future growth, then avg_row_len multiplied by the number of rows in the table (or number of rows you expect in the table) will be a good guide. 
But Oracle will leave some space free on each block, partly to allow for rows to 'grow' if they are updated, partly because it may not be possible to fit another entire row on that block (eg an 8K block would only fit 2 rows of 3K, though that would be an extreme example as 3K is a lot bigger than most row sizes).
So BLOCKS (in USER_TABLES) might be a better guide.

But if you had 200,000 rows in a table, deleted half of them, then the table would still 'own' the same number of blocks. It doesn't release them up for other tables to use.
Also, blocks are not added to a table individually, but in groups called an 'extent'. So there are generally going to be EMPTY_BLOCKS (also in USER_TABLES) in a table.

blocks|key|1483021|text|您可能会对此查询感兴趣。它告诉您在考虑索引和表上的任何LOB的情况下，为每个表分配了多少空间。通常，您感兴趣的不仅仅是表本身，而是“购买订单表占用了多少空间，包括任何索引”。你总是可以深入研究细节的。请注意，这需要访问DBA_*视图。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1483022|COLUMN+TABLE_NAME+FORMAT+A32
COLUMN+OBJECT_NAME+FORMAT+A32
COLUMN+OWNER+FORMAT+A10

SELECT
+++owner,+
+++table_name,+
+++TRUNC(sum(bytes)/1024/1024)+Meg,
+++ROUND(+ratio_to_report(+sum(bytes)+)+over+()+*+100)+Percent
FROM
(SELECT+segment_name+table_name,+owner,+bytes
+FROM+dba_segments
+WHERE+segment_type+IN+('TABLE',+'TABLE+PARTITION',+'TABLE+SUBPARTITION')
+UNION+ALL
+SELECT+i.table_name,+i.owner,+s.bytes
+FROM+dba_indexes+i,+dba_segments+s
+WHERE+s.segment_name+=+i.index_name
+AND+++s.owner+=+i.owner
+AND+++s.segment_type+IN+('INDEX',+'INDEX+PARTITION',+'INDEX+SUBPARTITION')
+UNION+ALL
+SELECT+l.table_name,+l.owner,+s.bytes
+FROM+dba_lobs+l,+dba_segments+s
+WHERE+s.segment_name+=+l.segment_name
+AND+++s.owner+=+l.owner
+AND+++s.segment_type+IN+('LOBSEGMENT',+'LOB+PARTITION')
+UNION+ALL
+SELECT+l.table_name,+l.owner,+s.bytes
+FROM+dba_lobs+l,+dba_segments+s
+WHERE+s.segment_name+=+l.index_name
+AND+++s.owner+=+l.owner
+AND+++s.segment_type+=+'LOBINDEX')
WHERE+owner+in+UPPER('&owner')
GROUP+BY+table_name,+owner
HAVING+SUM(bytes)/1024/1024+>+10++/*+Ignore+really+small+tables+*/
ORDER+BY+SUM(bytes)+desc
;|code-block|syntax|javascript|1483023|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|$]]

You might be interested in this query. It tells you how much space is allocated for each table taking into account the indexes and any LOBs on the table. Often you are interested to know "How much spaces the the Purchase Order table take, including any indexes" rather than just the table itself. You can always delve into the details. Note that this requires access to the DBA_* views.

<pre><code>COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10

SELECT
 owner, 
 table_name, 
 TRUNC(sum(bytes)/1024/1024) Meg,
 ROUND( ratio_to_report( sum(bytes) ) over () * 100) Percent
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND s.owner = i.owner
 AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND s.owner = l.owner
 AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND s.owner = l.owner
 AND s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&amp;owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 &gt; 10 /* Ignore really small tables */
ORDER BY SUM(bytes) desc
;
</code></pre>

blocks|key|1483066|text|根据块大小返回表的原始大小的简单select还包括size+with+index|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1483067|select+table_name，(nvl+((+select+sum(+blocks)+from+dba_indexes+a，dba_segments+b+where+a.index_name=b.segment_name+and+a.table_name=dba_tables.table_name+),0)%2Bblocks)*8192/1024+TotalSize，blocks*8+tableSize+from+dba_tables+order+by+3|1483068|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Simple select that returns the raw sizes of the tables, based on the block size, also includes size with index

select table_name,(nvl
((
select sum( blocks)
from dba_indexes a,dba_segments b
where a.index_name=b.segment_name
 and a.table_name=dba_tables.table_name
),0)+blocks)*8192/1024 TotalSize,blocks*8 tableSize
from dba_tables
order by 3

blocks|key|1698284|text|select+segment_name+as+tablename,+sum(bytes/+(1024+*+1024+*+1024))+as+tablesize_in_GB
From+dba_segments+/*+if+looking+at+tables+not+owned+by+you+else+use+user_segments+*/
where+segment_name+=+'TABLE_WHOSE_SIZE_I_WANT_TO_KNOW'
and+++OWNER+=+'WHO+OWNS+THAT+TABLE'+/*+if+user_segments+is+used+delete+this+line+*/+
group+by+segment_name+;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1698285|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 segment_name as tablename, sum(bytes/ (1024 * 1024 * 1024)) as tablesize_in_GB
From dba_segments /* if looking at tables not owned by you else use user_segments */
where segment_name = 'TABLE_WHOSE_SIZE_I_WANT_TO_KNOW'
and OWNER = 'WHO OWNS THAT TABLE' /* if user_segments is used delete this line */ 
group by segment_name ;
</code></pre>

blocks|key|1698302|text|我发现这个更准确一些：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1698303|SELECT
+++owner,+table_name,+TRUNC(sum(bytes)/1024/1024/1024)+GB
FROM
(SELECT+segment_name+table_name,+owner,+bytes
FROM+dba_segments
WHERE+segment_type+in++('TABLE','TABLE+PARTITION')
UNION+ALL
SELECT+i.table_name,+i.owner,+s.bytes
FROM+dba_indexes+i,+dba_segments+s
WHERE+s.segment_name+=+i.index_name
AND+++s.owner+=+i.owner
AND+++s.segment_type+in+('INDEX','INDEX+PARTITION')
UNION+ALL
SELECT+l.table_name,+l.owner,+s.bytes
FROM+dba_lobs+l,+dba_segments+s
WHERE+s.segment_name+=+l.segment_name
AND+++s.owner+=+l.owner
AND+++s.segment_type+IN+('LOBSEGMENT','LOB+PARTITION')
UNION+ALL
SELECT+l.table_name,+l.owner,+s.bytes
FROM+dba_lobs+l,+dba_segments+s
WHERE+s.segment_name+=+l.index_name
AND+++s.owner+=+l.owner
AND+++s.segment_type+=+'LOBINDEX')
---WHERE+owner+in+UPPER('&owner')
GROUP+BY+table_name,+owner
HAVING+SUM(bytes)/1024/1024+>+10++/*+Ignore+really+small+tables+*/
ORDER+BY+SUM(bytes)+desc|code-block|syntax|javascript|1698304|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|$]]

I found this to be a little more accurate:

<pre><code>SELECT
 owner, table_name, TRUNC(sum(bytes)/1024/1024/1024) GB
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type in ('TABLE','TABLE PARTITION')
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type in ('INDEX','INDEX PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type IN ('LOBSEGMENT','LOB PARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX')
---WHERE owner in UPPER('&amp;owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 &gt; 10 /* Ignore really small tables */
ORDER BY SUM(bytes) desc
</code></pre>

blocks|key|1698314|text|对于子分区的表和索引，我们可以使用以下查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1698315|++++SELECT+owner,+table_name,+ROUND(sum(bytes)/1024/1024/1024,+2)+GB
++++FROM
++++(SELECT+segment_name+table_name,+owner,+bytes
+++++FROM+dba_segments
+++++WHERE+segment_type+IN+('TABLE',+'TABLE+PARTITION',+'TABLE+SUBPARTITION')
+++++UNION+ALL
+++++SELECT+i.table_name,+i.owner,+s.bytes
+++++FROM+dba_indexes+i,+dba_segments+s
+++++WHERE+s.segment_name+=+i.index_name
+++++AND+++s.owner+=+i.owner
+++++AND+++s.segment_type+IN+('INDEX',+'INDEX+PARTITION',+'INDEX+SUBPARTITION')
+++++UNION+ALL
+++++SELECT+l.table_name,+l.owner,+s.bytes
+++++FROM+dba_lobs+l,+dba_segments+s
+++++WHERE+s.segment_name+=+l.segment_name
+++++AND+++s.owner+=+l.owner
+++++AND+++s.segment_type+=+'LOBSEGMENT'
+++++UNION+ALL
+++++SELECT+l.table_name,+l.owner,+s.bytes
+++++FROM+dba_lobs+l,+dba_segments+s
+++++WHERE+s.segment_name+=+l.index_name
+++++AND+++s.owner+=+l.owner
+++++AND+++s.segment_type+=+'LOBINDEX')
++++WHERE+owner+in+UPPER('&owner')
++++GROUP+BY+table_name,+owner
++++HAVING+SUM(bytes)/1024/1024+>+10++/*+Ignore+really+small+tables+*/
++++ORDER+BY+SUM(bytes)+DESC
++++;|code-block|syntax|javascript|1698316|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|$]]

For sub partitioned tables and indexes we can use the following query

<pre><code>

 SELECT owner, table_name, ROUND(sum(bytes)/1024/1024/1024, 2) GB
 FROM
 (SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND s.owner = i.owner
 AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND s.owner = l.owner
 AND s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND s.owner = l.owner
 AND s.segment_type = 'LOBINDEX')
 WHERE owner in UPPER('&owner')
 GROUP BY table_name, owner
 HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */
 ORDER BY SUM(bytes) DESC
 ;

</code></pre>

blocks|key|1483182|text|以下是WWs答案的一个变体，它包括上面其他人建议的分区和子分区，外加一个列来显示类型:表/索引/LOB等|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1483183|SELECT
+++owner,+"Type",+table_name+"Name",+TRUNC(sum(bytes)/1024/1024)+Meg
FROM
(++SELECT+segment_name+table_name,+owner,+bytes,+'Table'+as+"Type"
+++FROM+dba_segments
+++WHERE+segment_type+in++('TABLE','TABLE+PARTITION','TABLE+SUBPARTITION')
+UNION+ALL
+++SELECT+i.table_name,+i.owner,+s.bytes,+'Index'+as+"Type"
+++FROM+dba_indexes+i,+dba_segments+s
+++WHERE+s.segment_name+=+i.index_name
+++AND+++s.owner+=+i.owner
+++AND+++s.segment_type+in+('INDEX','INDEX+PARTITION','INDEX+SUBPARTITION')
+UNION+ALL
+++SELECT+l.table_name,+l.owner,+s.bytes,+'LOB'+as+"Type"
+++FROM+dba_lobs+l,+dba_segments+s
+++WHERE+s.segment_name+=+l.segment_name
+++AND+++s.owner+=+l.owner
+++AND+++s.segment_type+IN+('LOBSEGMENT','LOB+PARTITION','LOB+SUBPARTITION')
+UNION+ALL
+++SELECT+l.table_name,+l.owner,+s.bytes,+'LOB+Index'+as+"Type"
+++FROM+dba_lobs+l,+dba_segments+s
+++WHERE+s.segment_name+=+l.index_name
+++AND+++s.owner+=+l.owner
+++AND+++s.segment_type+=+'LOBINDEX')
+++WHERE+owner+in+UPPER('&owner')
GROUP+BY+table_name,+owner,+"Type"
HAVING+SUM(bytes)/1024/1024+>+10++/*+Ignore+really+small+tables+*/
ORDER+BY+SUM(bytes)+desc;|code-block|syntax|javascript|1483184|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|$]]

Heres a variant on WWs answer, it includes partitions and sub-partitions as others above have suggested, plus a column to show the TYPE: Table/Index/LOB etc

<pre><code>SELECT
 owner, "Type", table_name "Name", TRUNC(sum(bytes)/1024/1024) Meg
FROM
( SELECT segment_name table_name, owner, bytes, 'Table' as "Type"
 FROM dba_segments
 WHERE segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION')
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes, 'Index' as "Type"
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND s.owner = i.owner
 AND s.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION')
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes, 'LOB' as "Type"
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND s.owner = l.owner
 AND s.segment_type IN ('LOBSEGMENT','LOB PARTITION','LOB SUBPARTITION')
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes, 'LOB Index' as "Type"
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND s.owner = l.owner
 AND s.segment_type = 'LOBINDEX')
 WHERE owner in UPPER('&amp;owner')
GROUP BY table_name, owner, "Type"
HAVING SUM(bytes)/1024/1024 &gt; 10 /* Ignore really small tables */
ORDER BY SUM(bytes) desc;
</code></pre>

blocks|key|1698380|text|我修改了WW的查询以提供更详细的信息：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1698381|SELECT+*+FROM+(
++SELECT
++++owner,+object_name,+object_type,+table_name,+ROUND(bytes)/1024/1024+AS+meg,
++++tablespace_name,+extents,+initial_extent,
++++ROUND(Sum(bytes/1024/1024)+OVER+(PARTITION+BY+table_name))+AS+total_table_meg
++FROM+(
++++--+Tables
++++SELECT+owner,+segment_name+AS+object_name,+'TABLE'+AS+object_type,
++++++++++segment_name+AS+table_name,+bytes,
++++++++++tablespace_name,+extents,+initial_extent
++++FROM+++dba_segments
++++WHERE++segment_type+IN+('TABLE',+'TABLE+PARTITION',+'TABLE+SUBPARTITION')
++++UNION+ALL
++++--+Indexes
++++SELECT+i.owner,+i.index_name+AS+object_name,+'INDEX'+AS+object_type,
++++++++++i.table_name,+s.bytes,
++++++++++s.tablespace_name,+s.extents,+s.initial_extent
++++FROM+++dba_indexes+i,+dba_segments+s
++++WHERE++s.segment_name+=+i.index_name
++++AND++++s.owner+=+i.owner
++++AND++++s.segment_type+IN+('INDEX',+'INDEX+PARTITION',+'INDEX+SUBPARTITION')
++++--+LOB+Segments
++++UNION+ALL
++++SELECT+l.owner,+l.column_name+AS+object_name,+'LOB_COLUMN'+AS+object_type,
++++++++++l.table_name,+s.bytes,
++++++++++s.tablespace_name,+s.extents,+s.initial_extent
++++FROM+++dba_lobs+l,+dba_segments+s
++++WHERE++s.segment_name+=+l.segment_name
++++AND++++s.owner+=+l.owner
++++AND++++s.segment_type+=+'LOBSEGMENT'
++++--+LOB+Indexes
++++UNION+ALL
++++SELECT+l.owner,+l.column_name+AS+object_name,+'LOB_INDEX'+AS+object_type,
++++++++++l.table_name,+s.bytes,
++++++++++s.tablespace_name,+s.extents,+s.initial_extent
++++FROM+++dba_lobs+l,+dba_segments+s
++++WHERE++s.segment_name+=+l.index_name
++++AND++++s.owner+=+l.owner
++++AND++++s.segment_type+=+'LOBINDEX'
++)
++WHERE+owner+=+UPPER('&owner')
)
WHERE+total_table_meg+>+10
ORDER+BY+total_table_meg+DESC,+meg+DESC
/|code-block|syntax|javascript|1698382|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|$]]

I modified the WW's query to provide more detailed information:

<pre><code>SELECT * FROM (
 SELECT
 owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg,
 tablespace_name, extents, initial_extent,
 ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg
 FROM (
 -- Tables
 SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,
 segment_name AS table_name, bytes,
 tablespace_name, extents, initial_extent
 FROM dba_segments
 WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
 UNION ALL
 -- Indexes
 SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,
 i.table_name, s.bytes,
 s.tablespace_name, s.extents, s.initial_extent
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND s.owner = i.owner
 AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
 -- LOB Segments
 UNION ALL
 SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,
 l.table_name, s.bytes,
 s.tablespace_name, s.extents, s.initial_extent
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND s.owner = l.owner
 AND s.segment_type = 'LOBSEGMENT'
 -- LOB Indexes
 UNION ALL
 SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,
 l.table_name, s.bytes,
 s.tablespace_name, s.extents, s.initial_extent
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND s.owner = l.owner
 AND s.segment_type = 'LOBINDEX'
 )
 WHERE owner = UPPER('&amp;owner')
)
WHERE total_table_meg &gt; 10
ORDER BY total_table_meg DESC, meg DESC
/
</code></pre>

blocks|key|1483263|text|我有与上一个相同的变体，它计算表数据段、表索引和blob字段：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1483264|CREATE+OR+REPLACE+FUNCTION
++SYS.RAZMER_TABLICY_RAW(pNazvanie+in+varchar,+pOwner+in+varchar2)
return+number
is
++val+number(16);
++sz+number(16);
begin
++sz+:=+0;

++--Calculate+size+of+table+data+segments
++select
++++sum(t.bytes)+into+val
++from
++++sys.dba_segments+t
++where
++++t.segment_name+=+upper(pNazvanie)
++and
++++t.owner+=+upper(pOwner);
++sz+:=+sz+%2B+nvl(val,0);

++--Calculate+size+of+table+indexes+segments
++select
++++sum(s.bytes)+into+val
++from
++++all_indexes+t
++inner+join
++++dba_segments+s
++on
++++t.index_name+=+s.segment_name
++where
++++t.table_name+=+upper(pNazvanie)
++and
++++t.owner+=+upper(pOwner);
++sz+:=+sz+%2B+nvl(val,0);

++--Calculate+size+of+table+blob+segments
++select
++++sum(s.bytes)+into+val
++from
++++all_lobs+t
++inner+join
++++dba_segments+s+on+t.segment_name+=+s.segment_name
++where
++++t.table_name+=+upper(pNazvanie)
++and
++++t.owner+=+upper(pOwner);
++sz+:=+sz+%2B+nvl(val,0);

++return+sz;

end+razmer_tablicy_raw;|code-block|syntax|javascript|1483265|Source。|offset|length|1483266|entityMap|0|LINK|mutability|MUTABLE|url|http://konurbaev-e.blogspot.ru/2013/06/oracle-database-how-to-calculate-actual.html^0|0|0|0|6|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

I have the same variant as the last ones which calculates segments of table data, table indexes and blob-fields:

<pre><code>CREATE OR REPLACE FUNCTION
 SYS.RAZMER_TABLICY_RAW(pNazvanie in varchar, pOwner in varchar2)
return number
is
 val number(16);
 sz number(16);
begin
 sz := 0;

 --Calculate size of table data segments
 select
 sum(t.bytes) into val
 from
 sys.dba_segments t
 where
 t.segment_name = upper(pNazvanie)
 and
 t.owner = upper(pOwner);
 sz := sz + nvl(val,0);

 --Calculate size of table indexes segments
 select
 sum(s.bytes) into val
 from
 all_indexes t
 inner join
 dba_segments s
 on
 t.index_name = s.segment_name
 where
 t.table_name = upper(pNazvanie)
 and
 t.owner = upper(pOwner);
 sz := sz + nvl(val,0);

 --Calculate size of table blob segments
 select
 sum(s.bytes) into val
 from
 all_lobs t
 inner join
 dba_segments s on t.segment_name = s.segment_name
 where
 t.table_name = upper(pNazvanie)
 and
 t.owner = upper(pOwner);
 sz := sz + nvl(val,0);

 return sz;

end razmer_tablicy_raw;
</code></pre>

<a href="http://konurbaev-e.blogspot.ru/2013/06/oracle-database-how-to-calculate-actual.html" rel="nofollow">Source</a>.

blocks|key|1698508|text|还有一个选项允许使用joins获取"select“大小，表大小也可以作为选项|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1698509|--+1
EXPLAIN+PLAN
+++FOR
++++++SELECT
++++++++++++Scheme.Table_name.table_column1+AS+"column1",
++++++++++++Scheme.Table_name.table_column2+AS+"column2",
++++++++++++Scheme.Table_name.table_column3+AS+"column3",
++++++++++++FROM+Scheme.Table_name
+++++++WHERE+;

SELECT+*+FROM+TABLE+(DBMS_XPLAN.display);|code-block|syntax|javascript|1698510|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|$]]

there one more option that allows to get "select" size with joins, and table size as option too

<pre><code>-- 1
EXPLAIN PLAN
 FOR
 SELECT
 Scheme.Table_name.table_column1 AS "column1",
 Scheme.Table_name.table_column2 AS "column2",
 Scheme.Table_name.table_column3 AS "column3",
 FROM Scheme.Table_name
 WHERE ;

SELECT * FROM TABLE (DBMS_XPLAN.display);
</code></pre>

blocks|key|1483325|text|select+segment_name,segment_type,bytes/1024/1024+MB
from+dba_segments
where+segment_name='TABLENAME'+and+owner+='OWNERNAME'+order+by+mb+desc;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1483326|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 segment_name,segment_type,bytes/1024/1024 MB
from dba_segments
where segment_name='TABLENAME' and owner ='OWNERNAME' order by mb desc;
</code></pre>

blocks|key|1483376|text|我修改了查询以获取每个表空间的模式大小。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1483377|SELECT+owner,
+++++tablespace_name,
+++++TRUNC+(SUM+(bytes)+/+1024+/+1024)+++Meg,
+++++ROUND+(ratio_to_report+(SUM+(bytes))+OVER+()+*+100)+Percent
FROM+(SELECT+tablespace_name,+owner,+bytes
++++++++FROM+dba_segments
+++++++WHERE+segment_type+IN
+++++++++++++++++('TABLE',+'TABLE+PARTITION',+'TABLE+SUBPARTITION')
++++++UNION+ALL
++++++SELECT+i.tablespace_name,+i.owner,+s.bytes
++++++++FROM+dba_indexes+i,+dba_segments+s
+++++++WHERE+++++s.segment_name+=+i.index_name
+++++++++++++AND+s.owner+=+i.owner
+++++++++++++AND+s.segment_type+IN
+++++++++++++++++++++('INDEX',+'INDEX+PARTITION',+'INDEX+SUBPARTITION')
++++++UNION+ALL
++++++SELECT+l.tablespace_name,+l.owner,+s.bytes
++++++++FROM+dba_lobs+l,+dba_segments+s
+++++++WHERE+++++s.segment_name+=+l.segment_name
+++++++++++++AND+s.owner+=+l.owner
+++++++++++++AND+s.segment_type+IN+('LOBSEGMENT',+'LOB+PARTITION')
++++++UNION+ALL
++++++SELECT+l.tablespace_name,+l.owner,+s.bytes
++++++++FROM+dba_lobs+l,+dba_segments+s
+++++++WHERE+++++s.segment_name+=+l.index_name
+++++++++++++AND+s.owner+=+l.owner
+++++++++++++AND+s.segment_type+=+'LOBINDEX')
WHERE+owner+IN+UPPER+('&owner')
GROUP+BY+owner,+tablespace_name
--HAVING+SUM(bytes)/1024/1024+>+10++/*+Ignore+really+small+tables+*/
ORDER+BY+tablespace_name+--+desc
;|code-block|syntax|javascript|1483378|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|$]]

I modified the query to get the schema size per tablespace ..

<pre><code>SELECT owner,
 tablespace_name,
 TRUNC (SUM (bytes) / 1024 / 1024) Meg,
 ROUND (ratio_to_report (SUM (bytes)) OVER () * 100) Percent
FROM (SELECT tablespace_name, owner, bytes
 FROM dba_segments
 WHERE segment_type IN
 ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
 UNION ALL
 SELECT i.tablespace_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND s.owner = i.owner
 AND s.segment_type IN
 ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
 UNION ALL
 SELECT l.tablespace_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND s.owner = l.owner
 AND s.segment_type IN ('LOBSEGMENT', 'LOB PARTITION')
 UNION ALL
 SELECT l.tablespace_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND s.owner = l.owner
 AND s.segment_type = 'LOBINDEX')
WHERE owner IN UPPER ('&amp;owner')
GROUP BY owner, tablespace_name
--HAVING SUM(bytes)/1024/1024 &gt; 10 /* Ignore really small tables */
ORDER BY tablespace_name -- desc
;
</code></pre>

Being used to (and potentially spoiled by) <code>MSSQL</code>, I'm wondering how I can get at tables size in <code>Oracle</code> 10g.
I have googled it so I'm now aware that I may not have as easy an option as <code>sp_spaceused</code>. Still the potential answers I got are most of the time outdated or don't work. Probably because I'm no DBA on the schema I'm working with.
Would anyone have solutions and or recommendations?

How do I calculate tables size in Oracle

数据库管理

文件系统

SQL Server

数据库

由于习惯了MSSQL (并且可能会被它破坏)，我想知道如何才能在Oracle 10g中获得表的大小。我已经用谷歌搜索过了，所以我现在意识到我可能没有sp_spaceused那么简单的选择。尽管如此，我得到的潜在答案大多是过时的或不起作用的。可能是因为我不是正在使用的模式的DBA。有人会有解决方案和/或建议吗？

问如何在Oracle中计算表大小
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle中计算表大小EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle中计算表大小
EN