如何计算 Oracle 中的表大小 [英] How do I calculate tables size in Oracle

问题描述

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

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.

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

Would anyone have solutions and or recommendations?

推荐答案

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

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.

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
;

这篇关于如何计算 Oracle 中的表大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！