如何计算 Oracle 中的表大小

How do I calculate tables size in Oracle

提问by Rollo Tomazzi

Being used to (and potentially spoiled by) MSSQL, I'm wondering how I can get at tables size in Oracle 10g. I have googled it so I'm now aware that I may not have as easy an option as sp_spaceused. 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.

习惯于（并可能被）MSSQL，我想知道如何获得 Oracle 10g 中的表大小。我已经用谷歌搜索了它，所以我现在意识到我可能没有像 sp_spaceused 那样简单的选项。我得到的潜在答案大部分时间都已经过时或不起作用。可能是因为我不是我正在使用的模式的 DBA。

Would anyone have solutions and or recommendations?

有没有人有解决方案或建议？

回答by WW.

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.

您可能对此查询感兴趣。它会告诉您为每个表分配了多少空间，同时考虑了表上的索引和任何 LOB。通常，您想知道“采购订单表占用多少空间，包括任何索引”，而不仅仅是表本身。您可以随时深入了解细节。请注意，这需要访问 DBA_* 视图。

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
;

回答by grokster

-- Tables + Size MB
select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB 
from all_tables 
where owner not like 'SYS%'  -- Exclude system tables.
and num_rows > 0  -- Ignore empty Tables.
order by MB desc -- Biggest first.
;


--Tables + Rows
select owner, table_name, num_rows
 from all_tables 
where owner not like 'SYS%'  -- Exclude system tables.
and num_rows > 0  -- Ignore empty Tables.
order by num_rows desc -- Biggest first.
;

Note: These are estimates, made more accurate with gather statistics:

注意：这些是估计值，通过收集统计数据更加准确：

exec dbms_utility.analyze_schema(user,'COMPUTE');

回答by Justin Cave

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 OPTIMIZER_MODEis 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 USER_TABLES(or ALL_TABLESor DBA_TABLES) directly without calling GATHER_TABLE_STATS. If your statistics are not accurate, there is probably a reason for that and you don't want to disturb the status quo.

首先，我通常会警告说，为了进行空间分析而收集表统计信息是一件有潜在危险的事情。收集统计信息可能会改变查询计划，特别是如果 DBA 配置了一个使用您的调用未使用的非默认参数的统计信息收集作业，并且会导致 Oracle 重新解析使用相关表的查询，这可能会降低性能打。如果 DBA 故意留下一些没有统计信息的表（如果您OPTIMIZER_MODE是 CHOOSE则很常见），收集统计信息可能会导致 Oracle 停止使用基于规则的优化器并开始对一组查询使用基于成本的优化器，这可能是一个主要的性能如果在生产中意外完成，则令人头疼。如果您的统计数据准确，您可以查询USER_TABLES（ALL_TABLES或DBA_TABLES) 直接调用GATHER_TABLE_STATS. 如果您的统计数据不准确，那可能是有原因的，您不想扰乱现状。

Second, the closest equivalent to the SQL Server sp_spaceusedprocedure is likely Oracle's DBMS_SPACEpackage. Tom Kyte has a nice show_spaceprocedurethat provides a simple interface to this package and prints out information similar to what sp_spaceusedprints out.

其次，最接近 SQL Serversp_spaceused过程的可能是 Oracle 的DBMS_SPACE包。Tom Kyte 有一个很好的show_space过程，它为这个包提供了一个简单的界面，并打印出类似于打印出来的信息sp_spaceused。

回答by Tony Andrews

First, gather optimiser stats on the table (if you haven't already):

首先，收集表上的优化器统计信息（如果您还没有）：

begin
   dbms_stats.gather_table_stats('MYSCHEMA','MYTABLE');
end;
/

WARNING: As Justin says in his answer, gathering optimiser stats affects query optimisation and should not be done without due care and consideration!

警告：正如贾斯汀在他的回答中所说，收集优化器统计信息会影响查询优化，不应该在没有应有的谨慎和考虑的情况下进行！

Then find the number of blocks occupied by the table from the generated stats:

然后从生成的统计信息中找出该表占用的块数：

select blocks, empty_blocks, num_freelist_blocks
from   all_tables
where  owner = 'MYSCHEMA'
and    table_name = 'MYTABLE';

• The total number of blocks allocated to the table is blocks + empty_blocks + num_freelist_blocks.

• blocks is the number of blocks that actually contain data.

• 分配给表的总块数为 blocks + empty_blocks + num_freelist_blocks。

• blocks 是实际包含数据的块数。

Multiply the number of blocks by the block size in use (usually 8KB) to get the space consumed - e.g. 17 blocks x 8KB = 136KB.

将块数乘以正在使用的块大小（通常为 8KB）以获得消耗的空间 - 例如 17 个块 x 8KB = 136KB。

To do this for all tables in a schema at once:

要一次对架构中的所有表执行此操作：

begin
    dbms_stats.gather_schema_stats ('MYSCHEMA');
end;
/

select table_name, blocks, empty_blocks, num_freelist_blocks
from   user_tables;

Note: Changes made to the above after reading this AskTom thread

注意：阅读此 AskTom 线程后对上述内容所做的更改

回答by Sergey Stadnik

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

我修改了 WW 的查询以提供更详细的信息：

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
/

回答by rratina

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

对于子分区表和索引，我们可以使用以下查询

    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
    ;

回答by ConcernedOfTunbridgeWells

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.

IIRC 您需要的表是 DBA_TABLES、DBA_EXTENTS 或 DBA_SEGMENTS 和 DBA_DATA_FILES。如果您没有机器的管理权限，您可以查看这些表的 USER_ 和 ALL_ 版本。

回答by SS64

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

这是 WWs 答案的一个变体，它包括上面其他人建议的分区和子分区，加上一列显示类型：表/索引/LOB 等

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;

回答by bronx

select segment_name,segment_type,bytes/1024/1024 MB
from dba_segments
where segment_name='TABLENAME' and owner ='OWNERNAME' order by mb desc;

回答by Najee Ghanim

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

我修改了查询以获取每个表空间的架构大小..

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
;