oracle计算表的大小,如何计算Oracle中的表大小

您可能对此查询感兴趣。它告诉您为每个表分配了多少空间，同时考虑了索引和表上的任何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

;