如何像MySQL一样,查看表和索引的占用空间呢?

with t1 as
(
select
t.owner,
t.table_name,
i.index_name,
nvl((select round(sum(BYTES)/1024/1024/1024,2) G from dba_segments where SEGMENT_NAME=t.table_name AND OWNER=t.owner),0) tablesize,
nvl((select round(sum(BYTES)/1024/1024/1024,2) G from dba_segments where SEGMENT_NAME=i.index_name AND OWNER=i.owner ),0) indexsize
from dba_tables t
left join dba_indexes i on t.table_name=i.table_name
where t.owner like 'MVBOX%'
)
select
t1.owner as "所有者",
t1.table_name as "表名称",
t1.index_name as "索引名称",
t1.tablesize || 'G' as "表占用空间",
t1.indexsize || 'G' as "索引占用空间",
SUM(indexsize) over(PARTITION BY owner,table_name ) || 'G' as "索引占用总空间",
tablesize+SUM(indexsize) over(PARTITION BY owner,table_name ) || 'G' as "表和索引占用总空间"
from t1
order by tablesize desc,indexsize desc

ITPub博客

Oracle查看对象占用空间