如何在 Oracle 中计算表大小

我已经习惯了(并且可能会被 MSSQL破坏) ，我想知道如何才能获得 Oracle10g 的表格大小。 我已经谷歌了它，所以我现在意识到，我可能没有一个简单的选项作为 sp_spaceused。不过，我得到的可能的答案大多数时候已经过时或者不起作用。可能是因为我在使用的模式上不是 DBA。

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

513568 次浏览
小开

IIRC 需要的表是 DBA _ TABLES、 DBA _ EXTENT 或 DBA _ SEGMENT 和 DBA _ DATA _ FILES。如果您没有计算机上的管理权限，还可以看到这些表的 USER _ 和 ALL _ 版本。

小开

首先，收集桌面上更优化的统计数据(如果你还没有的话) :

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

警告: 正如 Justin 在他的回答中所说，收集更优化的统计数据会影响查询优化，如果没有适当的注意和考虑，就不应该这样做！

然后从生成的属性中查找表所占用的块数:

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

  • 分配给该表的块的总数为 block + void _ block + num _ freelist _ block。

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

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

一次为架构中的所有表执行此操作:

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


select table_name, blocks, empty_blocks, num_freelist_blocks
from   user_tables;

注: 读取 这个 AskTom 帖子后对上述内容所做的更改

小开

首先，我通常会提醒大家，为了进行空间分析而收集表统计信息是一件潜在的危险事情。收集统计信息可能会改变查询计划，特别是如果 DBA 已经配置了一个统计信息收集作业，该作业使用了您的调用没有使用的非默认参数，并且将导致 Oracle 重新解析利用有问题的表的查询，这可能会影响性能。如果 DBA 有意留下一些没有统计信息的表(如果您的 OPTIMIZER_MODE是 CHOOSE，这种情况很常见) ，那么收集统计信息可能会导致 Oracle 停止使用基于规则的优化器，而开始使用基于成本的优化器来处理一组查询，如果在生产环境中出现意外，这将是一个主要的性能问题。如果统计信息准确，则可以直接查询 USER_TABLES(或 ALL_TABLES或 DBA_TABLES)而不调用 GATHER_TABLE_STATS。如果你的统计数据不准确，这可能是有原因的，你不想打破现状。

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

小开

那要看你说的“桌子的大小”是什么意思了。 表与文件系统中的特定文件无关。一个表将驻留在一个表空间中(如果它是分区的，可能有多个表空间，如果您还想考虑表上的索引，则可能有多个表空间)。 一个表空间通常包含多个表，并且可能分布在多个文件中。

如果您正在估计表未来的增长需要多少空间，那么 avg _ row _ len 乘以表中的行数(或者您期望在表中的行数)将是一个很好的指南。 但是 Oracle 会在每个块上留出一些空间，部分是为了允许行在更新时“增长”，部分是因为可能无法在该块上容纳另一整行(例如，一个8K 块只能容纳2行3K 行，尽管这将是一个极端的例子，因为3K 行比大多数行大得多)。 因此，BLOCKS (在 USER _ TABLES 中)可能是一个更好的指南。

但是，如果一个表中有200,000行，删除了其中的一半，那么该表仍然“拥有”相同数量的块。它不会释放它们供其他桌子使用。 此外，块不是单独添加到表中，而是分组添加到称为“区段”的表中。因此，表中通常会有 EMPTY _ BLOCKS (也是 USER _ TABLES)。

小开

您可能对此查询感兴趣。它告诉您在考虑到索引和表上的任何 LOB 的情况下，为每个表分配了多少空间。通常，您感兴趣的是“ PurchaseOrder 表占用了多少空间，包括任何索引”，而不仅仅是表本身。你可以深入研究细节。注意，这需要访问 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
;
小开

根据块大小返回表的原始大小的简单选择还包括带索引的大小

Select table _ name，(nvl (( 选择和(块) 从 dba _ indexa，dba _  段 b 其中 a.index _ name = b.section _ name Table _ name = dba _ tables. table _ name ) ，0) + 块) * 8192/1024 TotalSize，block * 8 tableSize 来自 dba _ tables 3点订货

小开 
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 ;
小开 
-- 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.
;

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

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

我发现这个更准确一点:

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
小开

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




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
;
小开

这里有一个关于 WWs 答案的变体，它包括分区和子分区，正如上面其他人建议的那样，再加上一个列来显示 TYPE: Table/Index/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;
小开

我修改了 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
/
小开

我使用了与上一个相同的变体，它计算表数据、表索引和 blob 字段的片段:

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;

来源 。

小开

还有一个选项允许使用连接获取“选择”大小，表大小也作为选项

-- 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);
小开

已分区表的更正:

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 in ('LOBSEGMENT', 'LOB PARTITION', 'LOB SUBPARTITION')
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
;
小开 
select segment_name,segment_type,bytes/1024/1024 MB
from dba_segments
where segment_name='TABLENAME' and owner ='OWNERNAME' order by mb desc;
小开

我修改了查询以获取每个表空间的模式大小。

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
;

提交答案