如何在Oracle中计算表大小

本文介绍了如何在Oracle中计算表大小的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我已经习惯于MSSQL(并可能被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，为每个表分配了多少空间.通常，您很想知道"Purchase Order表占用了多少空间，包括任何索引"，而不仅仅是表本身.您总是可以深入研究细节.请注意，这需要访问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 ;